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

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

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

Simple command to disable all Foreign Key checks

After blogging about how to disable all Foreign Key constraints and the undocumented sp for looping through all tables in a database, I thought about combining the two.

So I’ve now got a much easier way to disable FK constraints in a database.
The script below will loop through each table and disable the FK check.

--Disable FK Checks
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

I’ve got an application that has a SQL CE offline mode where the Foreign Key keyword is not supported when running scripts against it.  (At least that’s what I’ve been told by the original developer).

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