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”]
Advertisements

List all indexes in a database

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 
[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 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 all tables in a database

If you ever have the need to list all tables in your database, here is a statement that you can use.
I find it helpful if you need to loop through all the tables in your database or sometimes I need to provide a list to someone else.  I have used this script when moving changes between environments as a quick check to make I didn’t forget table.s

There are many different reasons you may need a list tables.

This will return just the table name.

SELECT name AS TABLE_NAME 
FROM sys.tables ORDER BY name
[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”]

How to disable all Foreign Key constraints

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
BEGIN
SET @TableName =(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                FROM INFORMATION_SCHEMA.TABLES
                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 <> '')
    BEGIN
        EXEC ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
        PRINT ('ALTER TABLE '+ @TableName+ ' NOCHECK CONSTRAINT ALL;')
    END

END
GO
[tweetmeme only_single=”false”]