How to get a list of all identity columns in a database

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
cross apply
    (SELECT 
        column_name
         + 
           case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        ''
        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')
[tweetmeme only_single=”false”]

Summary of last night’s job run

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).

SELECT
    j.name, 
    last_run_outcome = 
    CASE 
        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' 
        ELSE 'Unknown' 
    END, 
    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
[tweetmeme only_single=”false”]

Get list of columns in every table

Here is a script to get a list of columns for each table.

SELECT t.name AS TABLE_NAME, c.name AS COLUMN_NAME, c.column_id AS COLUMN_ORDER, c.system_type_id AS DATA_TYPE, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity 
FROM sys.columns AS c 
INNER JOIN sys.tables AS t ON c.object_id = t.object_id
[tweetmeme only_single=”false”]

List all Primary Keys in a database

This script will list all primary keys in a database.

SELECT ST.name AS TABLE_NAME, SKC.name AS CONSTRAINT_NAME, SC.name AS CONSTRAINT_COLUMN_NAME, SC.column_id AS COLUMN_ORDER 
FROM sys.key_constraints AS SKC 
INNER JOIN sys.tables AS ST ON ST.object_id = SKC.parent_object_id 
INNER JOIN sys.index_columns AS SIC ON SIC.object_id = ST.object_id AND SIC.index_id = SKC.unique_index_id 
INNER JOIN sys.columns AS SC ON SC.object_id = ST.object_id AND SC.column_id = SIC.column_id WHERE (LEFT(ST.name, 1) = N't') AND (ST.type = N'U')
[tweetmeme only_single=”false”]

List all Foreign Keys in a database

Here is a script to list all foreign keys in a database.

SELECT o1.name AS TABLE_NAME, s.name AS CONSTRAINT_NAME, c1.name AS FOREIGN_KEY, o2.name AS FOREIGN_TABLE, c2.name AS FT_PRIMARY_KEY 
FROM sys.sysforeignkeys AS fk 
INNER JOIN sys.sysobjects AS o1 
ON fk.fkeyid = o1.id 
INNER JOIN sys.sysobjects AS o2 ON fk.rkeyid = o2.id 
INNER JOIN sys.syscolumns AS c1 ON c1.id = o1.id 
AND c1.colid = fk.fkey 
INNER JOIN sys.syscolumns AS c2 ON c2.id = o2.id AND c2.colid = fk.rkey 
INNER JOIN sys.sysobjects AS s ON fk.constid = s.id
[tweetmeme only_single=”false”]

List of all views and scripts to create

Here is a script to list all views in a database and provide the script to create them.  This helps when moving views to new environments.

SELECT a.name AS VIEW_NAME, LEFT(b.definition, 4000) AS SQL1, SUBSTRING(b.definition, 4001, 4000) AS SQL2, LEN(b.definition) AS SQL_Length 
FROM sys.sql_modules AS b 
INNER JOIN sys.views AS a ON b.object_id = a.object_id 
[tweetmeme only_single=”false”]

Get list of all default values for every table

This script will list all default values for every table.

SELECT ao.name AS TABLE_NAME, dc.name AS CONSTRAINT_NAME, dc.definition AS DEFAULT_VALUE, ac.name AS COLUMN_NAME 
FROM sys.default_constraints AS dc 
INNER JOIN sys.all_columns AS ac ON dc.parent_object_id = ac.object_id 
AND dc.parent_column_id = ac.column_id 
INNER JOIN sys.all_objects AS ao ON ac.object_id = ao.object_id

[tweetmeme only_single=”false”]

Query to get database information

Prashanth Jayaram provided a blog post about a script he has to get information about databases on your server.
This is a perfect script for keeping up with what’s going on in your environment or getting an idea about what’s going on a client’s site.

The script Prashanth provided had some errors, so I corrected those you can just copy the code below, paste it in SSMS and run it.

This is the information returned:
Servername
Database Name
[Status]
DataFiles
Data MB
LogFiles
Log MB
TotalSizeMB
Updateability
UserAccess
RecoveryModel
Version
Compatibility Level
Creation date
Last Backup Date

SELECT @@SERVERNAME Servername,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
(SELECT COUNT(1) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],
(SELECT COUNT(1) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],
(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB,
convert(sysname,DatabasePropertyEx(name,'Updateability'))  Updateability,
convert(sysname,DatabasePropertyEx(name,'UserAccess')) UserAccess ,
convert(sysname,DatabasePropertyEx(name,'Recovery')) RecoveryModel ,
convert(sysname,DatabasePropertyEx(name,'Version')) Version ,
CASE COMPATIBILITY_LEVEL 
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
END AS [compatibility level],
CONVERT(VARCHAR(20),  create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'
FROM msdb.dbo.backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME
[tweetmeme only_single=”false”]

Undocumented stored procedure for looping through all tables in a database

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'') 
    begin
        ALTER TABLE [?] ADD CreatedOn datetime NOT NULL DEFAULT getdate();
    end
'
[tweetmeme only_single=”false”]