>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’
Advertisements

Posted on May 26, 2011, in Scripts, Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 2 Comments.

  1. Jonathan Ausubel

    This solution will not work correctly for tables with concatenated keys. See example below:

    CREATE TABLE dbo.a (x1 int NOT NULL, x2 int NOT NULL, CONSTRAINT PK_a PRIMARY KEY (x1, x2))
    GO

    CREATE TABLE dbo.b (xx1 int, xx2 int, CONSTRAINT fk_a_b FOREIGN KEY (xx1, xx2) REFERENCES dbo.a (x1, x2))
    GO

    The add & drop queries will return the following results:

    ALTER TABLE dbo.b ADD CONSTRAINT fk_a_b FOREIGN KEY (xx1) REFERENCES dbo.a (x1)
    ALTER TABLE dbo.b ADD CONSTRAINT fk_a_b FOREIGN KEY (xx2) REFERENCES dbo.a (x2)

    ALTER TABLE dbo.b DROP CONSTRAINT fk_a_b
    ALTER TABLE dbo.b DROP CONSTRAINT fk_a_b

  2. Jonathan Ausubel

    Problem #2: If the columns in the referenced table are named differently from those in the referencing table, the first query will also return incorrect results.

    Here are new versions of the queries, which will resolve both issues:

    — Add foreign keys
    SELECT
    ‘ALTER TABLE ‘ + SCHEMA_NAME(f.[schema_id]) + ‘.’
    + OBJECT_NAME(f.[parent_object_id]) + ‘ ADD CONSTRAINT ‘ + f.[name]
    + ‘ FOREIGN KEY ‘ + ‘(‘
    + STUFF(
    (
    SELECT
    ‘, ‘
    + COL_NAME(fc.[parent_object_id], fc.[parent_column_id])
    FROM
    sys.foreign_key_columns fc
    WHERE
    fc.[constraint_object_id] = f.[object_id]
    ORDER BY fc.[constraint_column_id]
    FOR XML PATH(”)
    ), 1, 2, ”)
    + ‘)’
    + ‘ REFERENCES ‘ + SCHEMA_NAME(r.[schema_id]) + ‘.’
    + OBJECT_NAME(f.[referenced_object_id]) + ‘ (‘
    + STUFF(
    (
    SELECT
    ‘, ‘
    + COL_NAME(fc.[referenced_object_id], fc.[referenced_column_id])
    FROM
    sys.foreign_key_columns fc
    WHERE
    fc.[constraint_object_id] = f.[object_id]
    ORDER BY fc.[constraint_column_id]
    FOR XML PATH(”)
    ), 1, 2, ”)
    + ‘)’
    FROM
    sys.foreign_keys f
    JOIN
    sys.objects r
    ON r.[object_id] = f.[referenced_object_id]
    WHERE
    OBJECT_NAME(f.[parent_object_id]) = ‘b’

    –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 f
    WHERE
    OBJECT_NAME(f.[parent_object_id]) = ‘B’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: