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 ?”
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
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 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
This script will list all synonyms you have in a database. It will tell you the name, the server connecting to, the database, schema, and object name your connecting to.
This is helpful to figure out if you have your synonyms pointing to the wrong environment.
COALESCE(PARSENAME(base_object_name,4),@@servername) AS serverName,
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName,
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
PARSENAME(base_object_name,1) AS objectName
ORDER BY serverName,dbName,schemaName,objectName
Here is a script that will loop through all tables in your database and disable the foreign key constraints.
DECLARE @TableName nvarchar(256)
SET @TableName = ''
WHILE @TableName IS NOT NULL
SET @TableName =(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped' ) = 0)
IF (@TableName <> '')
EXEC ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
PRINT ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
I got tired of writing code to loop through all tables in a database. I found out about an undocumented stored procedure that does most of that for me.
The stored procedure name is sp_MSforeachtable it takes one input parameter (@command).
This simplifies so much code (no more looping through sysobjects).
Here are some examples of how to use it.
This command will return a row count for each table in your database.
EXEC sp_MSforeachtable ‘SELECT ‘‘?’‘, Count(*) as NumberOfRows FROM ?’
Notice the question mark (?), that gets substituted as the table (in the sp).
So basically any command you can do against a table you can use and put a ? where you put a table name.
Here is how to rebuild all indexes.
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
Here is how you update all statistics on your tables.
EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'
You can also add additional conditional logic if you need to restrict your commands to a subset of tables.
This is very powerful. You don’t always need to hit all tables with a command.
This one will loop through all your tables and add the Column CreatedOn on any table that doesn’t already have it.
EXEC sp_MSforeachtable '
if not exists (select column_name from INFORMATION_SCHEMA.columns
where table_name = ''?'' and column_name = ''CreatedOn'')
ALTER TABLE [?] ADD CreatedOn datetime NOT NULL DEFAULT getdate();
I presented at the Tulsa SQL Server user group on 4-9-2013. I spoke about support scripts for the app developers sql server toolbox.
For those that came, thanks for showing up and listening to me.
Attached is my presentation.
SQL Server Support Scripts
You will have to rename it from .pptx to .zip.
Wordpress won’t let up upload a zip file.
I wanted to include the sql scripts with the powerpoint presentation.
If you just want the powerpoint here is the link for that.
SQL Server Support Scripts
I’d love to hear any thoughts or feedback about it.