Thursday, May 26, 2011

SQL Server: Create/Drop Scripts for All Existing Foreign Keys


Today, when I need a script to get create and drop scripts of all existing foreign keys on a specific table (or in a whole database), I searched my query bank and find my required script quickly but on opening it I found that I was written for SQL Server 2000, time when there was no concept of schema, so I have made some changes so I can use it for SQL Server 2005 or 2008.
Create Foreign Keys:
SELECT  'ALTER TABLE ' + SCHEMA_NAME(F.schema_id) + '.'
        + OBJECT_NAME(F.parent_object_id) + ' ADD CONSTRAINT ' + F.name
        + ' FOREIGN KEY ' + '(' + COL_NAME(FC.parent_object_id,
                                           FC.parent_column_id) + ')'
        + ' REFERENCES ' + SCHEMA_NAME(RefObj.schema_id) + '.'
        + OBJECT_NAME(F.referenced_object_id) + ' ('
        + COL_NAME(FC.referenced_object_id, FC.referenced_column_id) + ')'
FROM    SYS.FOREIGN_KEYS AS F
        INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id
        INNER JOIN sys.objects RefObj ON RefObj.object_id = f.referenced_object_id
--WHERE   OBJECT_NAME(F.PARENT_OBJECT_ID) = 'YourObjectName'

Drop Foreign Keys

SELECT  'ALTER TABLE ' + SCHEMA_NAME(F.schema_id) + '.'
        + OBJECT_NAME(F.parent_object_id) + ' DROP CONSTRAINT ' + F.name
FROM    SYS.FOREIGN_KEYS AS F
        INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id
--WHERE   OBJECT_NAME(F.PARENT_OBJECT_ID) = 'YourObjectName'

at 9:21 PM
Labels: , , ,

4 comments:

  1. AnonymousSeptember 14, 2012 at 7:43 PM

    This doesn't work when you have multiple columns in the constraint. It scripts multiple alter statements one for each column.

    ReplyDelete
  2. UnknownDecember 7, 2012 at 12:07 PM

    Multi column and delete/update cascade stuff

    SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(F.schema_id)) + '.'
    + QUOTENAME(OBJECT_NAME(F.parent_object_id)) + ' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(F.name)
    + ' FOREIGN KEY ' + '(' + STUFF((
    SELECT ',' + QUOTENAME(COL_NAME(FC.parent_object_id, FC.parent_column_id))
    FROM SYS.FOREIGN_KEY_COLUMNS AS FC
    WHERE F.OBJECT_ID = FC.constraint_object_id
    ORDER BY FC.constraint_column_id
    FOR XML PATH('')
    ),1,1,'') + ')'
    + ' REFERENCES ' + QUOTENAME(SCHEMA_NAME(RefObj.schema_id)) + '.'
    + QUOTENAME(OBJECT_NAME(F.referenced_object_id)) + ' ('
    + STUFF((
    SELECT ',' + QUOTENAME(COL_NAME(FC.referenced_object_id, FC.referenced_column_id))
    FROM SYS.FOREIGN_KEY_COLUMNS AS FC
    WHERE F.OBJECT_ID = FC.constraint_object_id
    ORDER BY FC.constraint_column_id
    FOR XML PATH('')
    ),1,1,'') + ') '
    + CASE WHEN update_referential_action_desc = 'CASCADE' THEN ' ON UPDATE CASCADE'
    WHEN update_referential_action_desc = 'SET_DEFAULT' THEN ' ON UPDATE SET DEFAULT'
    WHEN update_referential_action_desc = 'SET_NULL' THEN ' ON UPDATE SET NULL'
    ELSE '' END
    + CASE WHEN delete_referential_action_desc = 'CASCADE' THEN ' ON DELETE CASCADE'
    WHEN delete_referential_action_desc = 'SET_DEFAULT' THEN ' ON DELETE SET DEFAULT'
    WHEN delete_referential_action_desc = 'SET_NULL' THEN ' ON DELETE SET NULL'
    ELSE '' END
    FROM SYS.FOREIGN_KEYS AS F
    INNER JOIN sys.objects RefObj ON RefObj.object_id = f.referenced_object_id
    --where OBJECT_NAME(f.parent_object_id) = 'yourObjectName'

    ReplyDelete
  3. AnonymousMarch 13, 2013 at 5:14 PM

    Thanks, just what I needed.

    ReplyDelete
  4. AnonymousApril 3, 2013 at 11:28 AM

    Thanks man, you saved my day!

    ReplyDelete

All suggestions are welcome

Subscribe to: Post Comments (Atom)