Restart identity field of table

DBCC CHECKIDENT('Customer', RESEED, 0)

Get List of all columns of table

so_columns tablename
for example :- sp_columns employee

or

SELECT   SysObjects.[Name] as TableName,  
    SysColumns.[Name] as ColumnName,  
    SysTypes.[Name] As DataType,  
    SysColumns.[Length] As Length  
FROM  
    SysObjects INNER JOIN SysColumns  
ON SysObjects.[Id] = SysColumns.[Id]  
    INNER JOIN SysTypes 
ON SysTypes.[xtype] = SysColumns.[xtype] 
WHERE  SysObjects.[type] = 'U' 
-- AND syscolumns.[name] LIKE 'FK%'
ORDER BY  SysObjects.[Name]

To fetch list of all foreign keys of table

CREATE VIEW ForeignKeyReferences
(fk_name, fk_table_name, fk_column_name,
key_name, key_table_name, key_column_name, ordinal_position)
AS
SELECT FK.constraint_name, FK.table_name, FKU.column_name,
UK.constraint_name, UK.table_name, UKU.column_name,
FKU.ordinal_position
FROM Information_Schema.Table_Constraints AS FK
INNER JOIN
Information_Schema.Key_Column_Usage AS FKU
ON FK.constraint_type = 'FOREIGN KEY' AND
FKU.constraint_name = FK.constraint_name
INNER JOIN
Information_Schema.Referential_Constraints AS RC
ON RC.constraint_name = FK.constraint_name
INNER JOIN
Information_Schema.Table_Constraints AS UK
ON UK.constraint_name = RC.unique_constraint_name
INNER JOIN
Information_Schema.Key_Column_Usage AS UKU
ON UKU.constraint_name = UK.constraint_name AND
UKU.ordinal_position =FKU.ordinal_position

SELECT *
FROM ForeignKeyReferences
ORDER BY fk_table_name, fk_name, ordinal_position

Find the Size of Database File and size of Log File

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Virtual_DuetEnterprise'
GO

Find column in all tables

SELECT distinct t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name--,
--c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%column name%'
ORDER BY schema_name, table_name;

Using CASE

update
    #temp
set
    esal =
case when esal >= 1000 or esal <= 2000 then esal + 1000
when esal >= 2000 or esal <= 5000 then esal + 2000
when esal >= 5000 or esal <= 10000 then esal + 3000
else
esal + 4000
end

Create New Line

declare @newline varchar(10)
set @newline = char(13) + char(10)

select 'a' + @newline + 'b'

-- to see the effect use shortcut ctrl + T

Genrate Random Number

SELECT CAST((RAND(CAST(NEWID() AS BINARY(6)))*500) As INT)

Script to backup all databases

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Delete duplicate records with CTE

WITH CTE_FriendRecruiters (RNo,FK_User,FK_Promotion,AuthId,AuthMode)
As
(
    SELECT ROW_NUMBER() over (partition by AuthId order by AuthId) As RNo ,
    FK_User,FK_Promotion,AuthId,AuthMode FROM dbo.FriendRecruiters
)

DELETE FROM CTE_FriendRecruiters where RNo <> 1

Genrate Random Number

SELECT CAST((RAND(CAST(NEWID() AS BINARY(6)))*500) As INT)

Reading XML

DECLARE @productXML AS XML

SET @productXML =
'<product>
 <data>
  <BoothId>22</BoothId>
  <Title>Product1</Title>
  <BoothProductLogo>a.jpg</BoothProductLogo>
  <UserBoothReps>
   <UserBoothRepId>7</UserBoothRepId>
   <UserBoothRepId>8</UserBoothRepId>
   <UserBoothRepId>9</UserBoothRepId>
  </UserBoothReps>
 </data>
 <data>
  <BoothId>22</BoothId>
  <Title>Product2</Title>
  <BoothProductLogo>b.jpg</BoothProductLogo>
  <UserBoothReps>
   <UserBoothRepId>23</UserBoothRepId>
   <UserBoothRepId>30</UserBoothRepId>
  </UserBoothReps>
 </data>
</product>'

SELECT
    p.c.query('.'),
    p.c.value('BoothId[1]', 'BIGINT') AS BoothId,
    p.c.value('Title[1]', 'VARCHAR(500)') AS Title,
    p.c.value('BoothProductLogo[1]', 'VARCHAR(200)') AS BoothProductLogo,
    u.c.value('.', 'INT') AS UserBoothRepId
FROM @productXML.nodes('/product/data') p(c)
    CROSS APPLY p.c.nodes('UserBoothReps/UserBoothRepId') u(c)

Reading XML with attributes

DECLARE @idoc int

DECLARE @doc varchar (1000)

SET @doc ='
<ROOT>
<ShipperRec Company="ABC Shippers" Ph="(503) 555-9191" />
</ROOT>'

--Create an internal representation of the XML document
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/ShipperRec',1)
WITH (Company varchar(80),
Ph varchar(48)
)

-- Clear the XML document from memory
EXEC sp_xml_removedocument @idoc

Create columns from Parent/Child records

select a.Category, b.* from #temp a cross apply
(select ISNULL((
                LTRIM(STUFF((SELECT
                     ', ' + tTg.Category
                FROM dbo.#temp (NoLock)tTg
                WHERE tTg.ParentId = a.id FOR XML PATH('')),1,1,''))
            ),'') as Category) b
where a.ParentId = 0

Find the size of log file in database

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DatabaseName'
GO

Reseed idenetity column

DBCC CHECKIDENT('Customer', RESEED, 0)

To switch User From Single User to Multi User

ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT

or

EXEC sp_dboption 'Works', 'single user', 'false'

EXEC sp_dboption 'Works', 'single user', 'true'

To list all the indexes of all tables

select SchemaName = schema_name(schema_id),
         TableName = object_name(t.object_id),
         ColumnName = c.name,
         [Unique] = CASE
                      WHEN i.is_unique = 1 THEN 'Yes'
                      ELSE 'No'
                    END,
         [Clustered] = CASE
                         WHEN i.index_id = 1 THEN 'C'
                         ELSE 'NC'
                       END,
         Ordinal = key_ordinal,
         IndexName = i.Name
 from sys.indexes i
         INNER JOIN sys.tables t
           ON i.object_id = t.object_id
         INNER JOIN sys.index_columns ic
           ON ic.object_id = t.object_id
              AND ic.index_id = i.index_id
         INNER JOIN sys.columns c
           ON c.object_id = t.object_id
              AND ic.column_id = c.column_id
         WHERE t.type = 'U'  order by [Clustered]

Grant Permissions to all objects

select 'GRANT EXECUTE ON OBJECT::' + name +  ' To DevUser;'
from sysobjects where xtype in ('P')
UNION ALL
select 'GRANT SELECT ON OBJECT::' + name +  ' To DevUser;'
from sysobjects where xtype in ('U')
UNION ALL
select 'GRANT INSERT ON OBJECT::' + name +  ' To DevUser;'
from sysobjects where xtype in ('U')
UNION ALL
select 'GRANT UPDATE ON OBJECT::' + name +  ' To DevUser;'
from sysobjects where xtype in ('U')
UNION ALL
select 'GRANT DELETE ON OBJECT::' + name +  ' To DevUser;'
from sysobjects where xtype in ('U')

Query to find any text in stored procedures

SELECT DISTINCT tSO.Name,tSO.Type_Desc FROM sys.sysComments tSC
JOIN sys.objects tSO ON tSO.Object_Id = tSC.Id
WHERE tSC.Text LIKE '%dbo.conferencemedia%'