Create a Comma Separated Column List for a SQL Server Table

declare @t as varchar(100)

declare @all as varchar(4000)

declare curs cursor for

select rtrim(c.name) + ', '

from sysobjects o

inner join syscolumns c on o.id = c.id

where o.xtype = 'u'

and o.name = 'table_name'

order by colid

open curs

fetch next from curs into @t

set @all = ''

while @@fetch_status = 0

begin

       set @all = @all + @t

       fetch next from curs into @t

end

close curs

deallocate curs

print @all

Average Run Time of a SQL Server Job

To get the average run time of a SQL Server job the below code will get that information from sysjobhistory.

SELECT

       name,

       start_time,

       dateadd(s, avg(run_duration_seconds), start_time) as average_end_time,

       dateadd(s, max(run_duration_seconds), start_time) as max_end_time,

       dateadd(s, min(run_duration_seconds), start_time) as min_end_time

FROM

       (SELECT

              j.name,

              cast('01/01/1900 ' +

              substring(right(('000000') + cast(jh.run_time as varchar), 6), 1, 2) + ':' +

              substring(right(('000000') + cast(jh.run_time as varchar), 6), 3, 2) + ':' +

              substring(right(('000000') + cast(jh.run_time as varchar), 6), 5, 2) as smalldatetime) as start_time,

              substring(right(('000000') + cast(jh.run_duration as varchar), 6), 5, 2) +

              (substring(right(('000000') + cast(jh.run_duration as varchar), 6), 3, 2) * 60) +

              (substring(right(('000000') + cast(jh.run_duration as varchar), 6), 1, 2) * 60 * 60) as run_duration_seconds

       FROM sysjobs j

              INNER JOIN sysjobhistory jh on j.job_id = jh.job_id

       WHERE

              datepart(weekday, cast(substring(cast(jh.run_date as varchar), 5, 2) + '-' + substring(cast(jh.run_date as varchar), 7, 2) + '-' + substring(cast(jh.run_date as varchar), 1, 4) as smalldatetime)) in (2,3,4,5,6)

WHERE run_duration_seconds > 120

GROUP BY

       name,

       start_time

ORDER BY

       name

Script out Permissions

If you want to copy permissions from one database to another the following script will handle migration of roles, role members, object permissions, and database owners.

set nocount on

declare @DBName varchar(100)

DECLARE curs CURSOR for

SELECT distinct name

FROM master.dbo.SYSDATABASES

where dbid > 4

OPEN CURS

FETCH Next FROM CURS into @dbname

WHILE @@FETCH_STATUS = 0

BEGIN     

        

       EXEC('         

        SELECT ''IF NOT EXISTS(SELECT 1 FROM ' + @DBName + '..sysusers where name='''''' + sysusers.Name + '''''')'' + CHAR(13) +  ''EXEC ' + @DBName + '..sp_grantdbaccess '''''' + syslogins.name + '''''', '''''' + sysusers.name + ''''''''         

         FROM ' + @DBName + '..sysusers sysusers         

         JOIN master..syslogins syslogins         

           ON syslogins.sid=sysusers.sid         

        WHERE issqlrole=0         

          AND sysusers.Name Not in (''Guest'', ''dbo'')         

       ')     

            

       --Roles     

       EXEC('     

       SELECT ''IF NOT EXISTS(SELECT 1 FROM ' + @DBName + '..sysusers where name='''''' + Name + '''''' and issqlrole=1)'' + CHAR(13) + ''exec ' + @DBName + '..sp_addrole '''''' + Name + '''''', ''''dbo''''''     

         FROM ' + @DBName + '..sysusers      

        WHERE issqlrole=1      

          AND name not like ''db_%''     

       ')     

            

       --Role Members         

            

       EXEC('         

       SELECT ''EXEC ' + @DBName + '..sp_addrolemember '''''' + role.name + '''''', '''''' + member.name + ''''''''         

         FROM ' + @DBName + '..sysmembers sysmembers         

         JOIN ' + @DBName + '..sysusers role         

           ON groupuid=role.uid         

         JOIN ' + @DBName + '..sysusers member         

           ON memberuid=member.uid         

        WHERE member.name<>''dbo''         

       ')     

             

       --Object Permissions         

       EXEC('         

       SELECT ''USE ' + @DBName + ''' + CHAR(13) +      

        ''IF EXISTS(SELECT 1 FROM ' + @DBName + '..sysobjects where name='''''' + o.name + '''''')'' + CHAR(13) +      

        ''GRANT '' + v.name + '' ON '' + u2.name + ''.'' + o.name + '' TO ['' + u1.name + '']''     

         FROM ' + @DBName + '..sysprotects p          

         JOIN master..spt_values v         

           ON action = v.number          

          AND v.type = ''T''         

         JOIN ' + @DBName + '..sysobjects o         

           ON o.id = p.id         

         JOIN ' + @DBName + '..sysusers u1         

           ON u1.uid=p.uid         

         JOIN ' + @DBName + '..sysusers u2         

           ON u2.uid=o.uid         

        WHERE p.id > 100         

          AND protecttype IN  (204,205)         

       ')     

            

       --Set DB owner     

         

            

        EXEC('    

        SELECT ''exec ' + @DBName + '..sp_changedbowner ''''sa''''''     

        ')   

      

       FETCH Next FROM CURS into @dbname

END     

   

close curs     

DEALLOCATE curs

   

 

 

 

 

 

 

 

Dynamically Generate sp_attach_db Statement Prior to removing a SQL Server Database

--script to generate the attach statement for a database

declare @dbname as sysname

declare @sql as varchar(1000)

declare @n as integer

declare @filename as sysname

set @dbname = db_name()

declare curs cursor for

select rtrim(filename) from sysfiles

open curs

fetch next from curs into @filename

set @n = 0

print '--ATTACH SCRIPT FOR ' + @dbname

print 'sp_attach_db @dbname = N''' + @dbname + ''','

while @@fetch_status = 0

begin

       set @n = @n + 1

       print '@filename' + cast(@n as varchar) + ' = ''' + @filename + ''','

       fetch next from curs into @filename     

end

close curs

deallocate curs

Convert the run-date, run_time, and run_duration of a SQL Server job to start and stop times

The below code will allow you to view the start and stop times of SQL Server jobs

SELECT

j.name,

jh.step_name,

jh.message,

jh.server,

t.start_time,

DATEADD(second, t.duration_seconds, t.start_time) as end_time,

t.duration_seconds

FROM

(      SELECT

       instance_id,

       CAST(

       --get the date

       SUBSTRING(CAST(run_date as VARCHAR), 5, 2) + '/' +

       SUBSTRING(CAST(run_date as VARCHAR), 7, 2) + '/' +

       SUBSTRING(CAST(run_date as VARCHAR), 1, 4) + ' ' +

       --get the time

       SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR), 6), 1, 2) + ':' +

       SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR), 6), 3, 2) + ':' +

       SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR), 6), 5, 2) as SMALLDATETIME) as start_time,

       --get duration in seconds

       SUBSTRING(RIGHT('000000' + CAST(run_duration as VARCHAR), 6), 1, 2) * (60 * 60) +

       SUBSTRING(RIGHT('000000' + CAST(run_duration as VARCHAR), 6), 3, 2) * (60) +

       SUBSTRING(RIGHT('000000' + CAST(run_duration as VARCHAR), 6), 5, 2) as duration_seconds

       FROM msdb.dbo.sysjobhistory) t

INNER JOIN msdb.dbo.sysjobhistory jh on t.instance_id = jh.instance_id

INNER JOIN msdb.dbo.sysjobs j on j.job_id = jh.job_id

ORDER BY t.start_time desc

Largest Tables in a SQL Server Database

Use the query below in any database to identify the largest tables in that database.

SELECT

t.NAME AS TableName,

s.Name AS SchemaName,

(SUM(a.total_pages) * 8)  AS TotalSpaceKB,

(SUM(a.total_pages) * 8) /1024 / 1024 AS TotalSpaceGB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE 'dt%'

GROUP BY t.Name, s.Name

ORDER BY (SUM(a.total_pages) * 8) desc

Diagnosing RESOURCE_SEMAPHORE wait types

Diagnosing memory problems on SQL server can be difficult.  If you want to see if your SQL Server is running low on memory check out sys.dm_exec_query_memory_grants.  Checking the requested_memory_kb will show you the number of queries that are currently waiting on memory to be granted and how much memory they are requesting.

This is a useful query to use if you see a lot of waittypes of RESOURCE_SEMAPHORE.