For some reason my when I create new tables in my development environment all the tables have my username as the schema owner (instead of dbo).
So I came up with a quick script to reset all the tables to dbo. I do this because it can cause problems if tables have different schema owners and someone doesn’t have access to see what’s in your schema (like another developer).
exec sp_MSforeachtable “ALTER SCHEMA dbo TRANSFER ?”
Sometimes operations (like drop, etc) will fail on your DB if someone running a command and locking a resource.
So I came up (or found somewhere) a query to tell me what’s currently running.
SELECT P.spid as SPID, P.kpid as KPID, P.dbid as DBID, D.name, convert(varchar, P.last_batch, 5) as StartDate, convert(varchar, P.last_batch, 8) as StartTime,
P.hostname as HostName, P.nt_domain as Domain, P.nt_username as Username,P.cmd as Command, P.waitresource, P.status as [Status],P.cpu as CPU,P.memusage as MEM,P.physical_io as IO
FROM sys.sysprocesses P
JOIN sys.databases D
ON P.dbid = D.database_id
ORDER BY D.name, P.cpu
This will return all the processes, sometimes you just want the processes for a specific user. Then you just need to add a where clause.
WHERE nt_username = ‘<username>’
Here is script to get the file sizes of your db files:
The results are ordered by the file size descending.
So you can see what’s taking up all your hard drive space.
select physical_name, size, CONVERT(DECIMAL(10,2),(size * 8.00) / 1024.00) As UsedSpace
order by size desc
Here is a script to check index fragmentation. This is helpful if you need to determine if you need to rebuild indexes.
SELECT dbtables.[name] as ‘Table’,
dbindexes.[name] as ‘Index’,
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
Here is some code to get a list of identity columns in database.
select so.name as TableName, + o.list as IdentityColumnName
from sysobjects so
case when exists (
select id from syscolumns
and columnproperty(id,name,'IsIdentity') = 1
end + ' '
from information_schema.columns where table_name = so.name
) o (list)
where xtype = 'U'
AND o.list is not null
AND name NOT IN ('dtproperties')
Here is some code to use to check if a database exists.
Just replace the string ‘DATABASE NAME’.
SELECT * FROM [master].[sys].[databases] WHERE name='DATABASE NAME'
Thanks to Tulsa Tech Fest for letting me present.
Attached is my presentation about SQL Support Scripts.
In the notes are the links to the scripts.
Tulsa TechFest 2013 Presentation
I hope you find something valuable to use.
This script will list all indexes and their types (clustered/nonclustered).
SELECT so.name AS TableName
, si.name AS IndexName
, si.type_desc AS IndexType
FROM sys.indexes si
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE so.type = 'U' --Only get indexes for User Created Tables
AND si.name IS NOT NULL
ORDER BY so.name, si.type
This script will provide a list of all stored procedures in a database along with the create script for each.
SELECT specific_name, routine_definition
WHERE routine_type = 'PROCEDURE'
Here is a script that will provide a summary of jobs that ran last night.
This is helpful if you want a summary of your jobs instead of an email from each job.
(You can setup one job to just email you the results of this script).
WHEN js.last_run_outcome = 0 THEN 'Failed'
WHEN js.last_run_outcome = 1 THEN 'Succeeded'
WHEN js.last_run_outcome = 2 THEN 'Retry'
WHEN js.last_run_outcome = 3 THEN 'Canceled'
last_run_datetime = msdb.dbo.agent_datetime(
CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END,
CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END)
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
WHERE msdb.dbo.agent_datetime(CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END,
CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END) > DATEADD (dd , -1 , GETDATE())
ORDER BY Name