Peter Larsson Blog

Patron Saint of Lost Yaks

Script out your foreign keys

by Peter Larsson in  , ,

This is a simple query for creating a script for your foreign keys in your database. It may need tweaking for composite keys.
If that's the case, see here how to concatenate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

;WITH Yak
AS (
    SELECT      ROW_NUMBER() OVER (ORDER BY o.[schema_id]) AS RowID,
                QUOTENAME(o.name) AS CONSTRAINT_NAME,
                QUOTENAME(SCHEMA_NAME(po.[schema_id])) AS FOREIGN_TABLE_SCHEMA,
                QUOTENAME(po.name) AS FOREIGN_TABLE_NAME,
                QUOTENAME(rccu.COLUMN_NAME) AS FOREIGN_COLUMN_NAME,
                QUOTENAME(SCHEMA_NAME(ro.[schema_id])) AS PRIMARY_TABLE_SCHEMA,
                QUOTENAME(ro.name) AS PRIMARY_TABLE_NAME,
                QUOTENAME(rc.name) AS PRIMARY_COLUMN_NAME,
                CASE fk.is_disabled
                    WHEN 0 THEN 'CHECK'
                    ELSE 'NOCHECK'
                END AS [ENABLED]
    FROM        sys.foreign_keys AS fk
    INNER JOIN sys.objects AS o ON o.[object_id] = fk.[object_id]
    INNER JOIN sys.objects AS po ON po.[object_id] = fk.parent_object_id
    INNER JOIN sys.objects AS ro ON ro.[object_id] = fk.referenced_object_id
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS rccu ON rccu.CONSTRAINT_SCHEMA = SCHEMA_NAME(o.[schema_id])
                    AND rccu.CONSTRAINT_NAME = o.name
                    AND rccu.TABLE_SCHEMA = SCHEMA_NAME(po.[schema_id])
                    AND rccu.TABLE_NAME = po.name
    INNER JOIN sys.index_columns AS ric ON ric.[object_id] = fk.referenced_object_id
                    AND ric.index_id = fk.key_index_id
                    AND ric.is_included_column = 0
    INNER JOIN sys.columns AS rc ON rc.[object_id] = fk.referenced_object_id
                    AND rc.column_id = ric.column_id
)
 
SELECT      Command
FROM        (
                SELECT 2 * RowID - 1 AS RowID,
                        'ALTER TABLE ' + FOREIGN_TABLE_SCHEMA + '.' + FOREIGN_TABLE_NAME
                        + ' WITH CHECK ADD CONSTRAINT ' + CONSTRAINT_NAME
                        + ' FOREIGN KEY(' + FOREIGN_COLUMN_NAME + ')'
                        + ' REFERENCES ' + PRIMARY_TABLE_SCHEMA + '.' + PRIMARY_TABLE_NAME
                        + '(' + PRIMARY_COLUMN_NAME + ')' AS Command
                FROM    Yak
 
                UNION ALL
 
                SELECT 2 * RowID,
                        'ALTER TABLE ' + FOREIGN_TABLE_SCHEMA + '.' + FOREIGN_TABLE_NAME
                        + ' ' + [ENABLED] + ' CONSTRAINT ' + CONSTRAINT_NAME
                FROM    Yak
            ) AS d
ORDER BY    RowID

Legacy Comments
Uri Dimant
2009-08-09		 re: Script out your foreign keys
We can also check for trusted FKs (for performance benefit)

SELECT OBJECT_NAME(parent_object_id) AS table_name, name
FROM sys.check_constraints
WHERE is_not_trusted = 1
UNION ALL
SELECT OBJECT_NAME(parent_object_id) AS table_name, name
FROM sys.foreign_keys
WHERE is_not_trusted = 1
ORDER BY table_name