Insert Record(s) to a Table from Another Table

insert into TableDocument2(DocumentId, DeptId, a, b, c, d,e) 
select DocumentId, 9, f, g, h, i, NULL from TableDocument

Get "2 days before" Dates

Get "2 days before" dates
SELECT * FROM Table1 WHERE DATEDIFF(day, CURRENT_TIMESTAMP, SampleDateField) = 2; 

Another sample usage of DATEDIFF
SELECT DATEDIFF(day, CURRENT_TIMESTAMP, SampleDateField) as 'Days' from Table1

Extract Numbers from String

Declare @s varchar(100),@result varchar(100)
set @s='lkjflkjd-sflsakjqj098yvak8751'
set @result=''
select @result=@result + (case when number like '[0-9]' then number else '' end)
from (select substring(@s,number,1) as number from (select number from master..spt_values where type='p' and number between 1 and len(@s)) as t) as t
select @result as only_numbers

Result:
0988751

determine the number of rows for each table in the db

-- i like this best
SELECT t.name, SUM(rows) AS Rows
FROM sys.tables t JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN(0,1) and t.name like 'tbl%'
GROUP BY t.name


-- can be used but only 100 can be displayed
sp_msforeachtable 'select ''?'', count (*) from ?'

loop thru db tables, check if particular column exists, then update the existing column

declare @tables Table(
id int identity(1,1),
name varchar(200)
)
insert into @tables(name)
SELECT name FROM sysobjects WHERE type = 'U'
declare @sql_string nvarchar(4000)
declare @name varchar(200), @min int, @max int
select @min=1
select @max=MAX(id) from @tables
while(@min <= @max)
begin
select @name = name from @tables where id = @min
if (exists(select * from information_schema.columns where table_name = @name and (column_name='editBy')) and
exists(select * from information_schema.columns where table_name = @name and (column_name='editDate')))
begin
set @sql_string = 'Update ' + @name + ' SET editBy=''engine upgrade'', editDate=getdate()'
--print @name
exec sp_executesql @sql_string
end
set @min = @min + 1
end

Delete all data from the database

1. Run this script:
SELECT 'DELETE FROM ' + name FROM sysobjects WHERE type = 'U'

2. Copy the result in the query window; then, execute
DELETE FROM tblAccommodationProductFacilityCategory
DELETE FROM tblProductClass
DELETE FROM tblVehicleDriveType
DELETE FROM tblCommunicationType
DELETE FROM tblAccommodationProductFacility
DELETE FROM tblVehicleRadioType
DELETE FROM tblAccommodationProductFacilityDescription
DELETE FROM tblVehicleType
...

3. Repeat over and over again until there is no error remaining

Retrieve XML data via Sql Server

ALTER PROCEDURE [dbo].Select_AccommodationMediaTypeSupplierByIds (@idsXML varchar(5000))
AS
DECLARE @ids int
EXEC sp_xml_preparedocument @ids OUTPUT, @idsXML
SELECT DISTINCT SupplierAccommodationMedia.*
FROM SupplierAccommodationMedia
WHERE SupplierAccommodationMedia.accommodationMediaTypeId IN (SELECT * FROM OpenXml(@ids,'/ids/id') WITH (v INT))
EXEC sp_xml_removedocument @ids

More info:
http://dotnetslackers.com/articles/xml/Dynamic_XML_From_SQL_Server.aspx
http://msdn.microsoft.com/en-us/library/ms178088.aspx
http://msdn.microsoft.com/en-us/library/ms186918.aspx

Display schema and constraints

SELECT C.CONSTRAINT_NAME ,PK.TABLE_NAME as PK_TABLE_NAME , PK.COLUMN_NAME as PK_COLUMN_NAME
, FK.TABLE_NAME as FK_TABLE_NAME, FK.COLUMN_NAME as FK_COLUMN_NAME
FROM Information_Schema.REFERENTIAL_CONSTRAINTS C
JOIN Information_Schema.KEY_COLUMN_USAGE PK
ON PK.CONSTRAINT_NAME = C.UNIQUE_CONSTRAINT_NAME
JOIN Information_Schema.KEY_COLUMN_USAGE FK
ON FK.CONSTRAINT_NAME = C.CONSTRAINT_NAME
--WHERE (((PK.TABLE_NAME ='Customers') and (FK.TABLE_NAME ='Orders'))
--OR ((PK.TABLE_NAME ='Orders') and (FK.TABLE_NAME ='Customers')))
Select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME from Information_Schema.KEY_COLUMN_USAGE where
TABLE_NAME = 'Orders'
Select CONSTRAINT_NAME, UNIQUE_CONSTRAINT_NAME from Information_Schema.REFERENTIAL_CONSTRAINTS
select * from INFORMATION_SCHEMA.Columns where Table_Name = 'Orders'

Display the description of tables and columns

select *
from sys.tables t
left outer join sys.extended_properties x on x.major_id = t.object_id
order by t.name