Script for all Foreign Keys of all tables of a DB

  • Henk Schreij

    SSCarpal Tunnel

    Points: 4553

    Comments posted to this topic are about the item Script for all Foreign Keys of all tables of a DB

  • neongladiator

    SSC-Addicted

    Points: 407

    I recently needed to script out all foreign keys in a database, and figured I would post that script.

    SELECT 'ALTER TABLE [' + RTRIM(tabowner.name) + '].[' + RTRIM(tab.name) + '] ADD CONSTRAINT ['

    + RTRIM(fkey.name) + '] FOREIGN KEY ([' + RTRIM(col.name) + ']) REFERENCES ['

    + RTRIM(rtabowner.name) + '].[' + RTRIM(rtab.name) + '] ([' + RTRIM(rcol.name) + '])'

    FROM sysforeignkeys sfk (NOLOCK)

    JOIN sysobjects tab (NOLOCK)

    ON tab.id = sfk.fkeyid

    JOIN sysobjects rtab (NOLOCK)

    ON rtab.id = sfk.rkeyid

    JOIN syscolumns col (NOLOCK)

    ON col.id = tab.id

    AND col.colid = sfk.fkey

    JOIN syscolumns rcol (NOLOCK)

    ON rcol.id = rtab.id

    AND rcol.colid = sfk.rkey

    JOIN sysobjects fkey (NOLOCK)

    ON fkey.id = sfk.constid

    JOIN sysusers tabowner (NOLOCK)

    ON tabowner.uid = tab.uid

    JOIN sysusers rtabowner (NOLOCK)

    ON rtabowner.uid = rtab.uid

  • MudLuck

    SSCrazy

    Points: 2271

    Hey Neon,

    How do you account for FK that have more then one column? This query wont work for those. food for thought.

  • neongladiator

    SSC-Addicted

    Points: 407

    Hi Mud. I forgot to post the new version that I made once I remembered that not all FKs have only one column. Below is a longer version of the script that will account for any number of columns in the foreign keys.

    --Declare variables used to concatenate column lists

    DECLARE @column INT

    , @maxcolumns INT

    --Drop temp tables if they already exist

    IF OBJECT_ID('tempdb..#foreignkeycolumns') IS NOT NULL DROP TABLE #foreignkeycolumns

    IF OBJECT_ID('tempdb..#foreignkeys') IS NOT NULL DROP TABLE #foreignkeys

    --Gather data for foreign key columns

    SELECT FKeyID = fkey.id

    , FKeyName = fkey.name

    , TabOwner = tabowner.name

    , TabID = tab.id

    , TabName = tab.name

    , ColName = col.name

    , RTabOwner = rtabowner.name

    , RTabID = rtab.id

    , RTabName = rtab.name

    , RColName = rcol.name

    , KeyNo = sfk.keyno

    INTO #foreignkeycolumns

    FROM sysforeignkeys sfk (NOLOCK)

    JOIN sysobjects tab (NOLOCK)

    ON tab.id = sfk.fkeyid

    JOIN sysobjects rtab (NOLOCK)

    ON rtab.id = sfk.rkeyid

    JOIN syscolumns col (NOLOCK)

    ON col.id = tab.id

    AND col.colid = sfk.fkey

    JOIN syscolumns rcol (NOLOCK)

    ON rcol.id = rtab.id

    AND rcol.colid = sfk.rkey

    JOIN sysobjects fkey (NOLOCK)

    ON fkey.id = sfk.constid

    JOIN sysusers tabowner (NOLOCK)

    ON tabowner.uid = tab.uid

    JOIN sysusers rtabowner (NOLOCK)

    ON rtabowner.uid = rtab.uid

    --Find distinct foreign keys

    SELECT DISTINCT

    FKeyID = FKeyID

    , FKeyName = FKeyName

    , TabOwner = TabOwner

    , TabID = TabID

    , TabName = TabName

    , RTabOwner = RTabOwner

    , RTabID = RTabID

    , RTabName = RTabName

    , Columns = CONVERT(VARCHAR(500),NULL)

    , RColumns = CONVERT(VARCHAR(500),NULL)

    INTO #foreignkeys

    FROM #foreignkeycolumns

    --Find max number of columns in any foreign key

    SELECT @maxcolumns = MAX(KeyNo)

    FROM #foreignkeycolumns

    SET @column = 1

    --Find first column in foreign key

    UPDATE #foreignkeys

    SET Columns = '[' + RTRIM(fkc.ColName) + ']'

    , RColumns = '[' + RTRIM(fkc.RColName) + ']'

    FROM #foreignkeys fk

    JOIN #foreignkeycolumns fkc

    ON fk.FKeyID = fkc.FKeyID

    WHERE fkc.KeyNo = @column

    --Concatenate list of columns for foreign keys

    WHILE @column < @maxcolumns

    BEGIN

    SET @column = @column + 1

    UPDATE #foreignkeys

    SET Columns = Columns + ',[' + RTRIM(fkc.ColName) + ']'

    , RColumns = RColumns + ',[' + RTRIM(fkc.RColName) + ']'

    FROM #foreignkeys fk

    JOIN #foreignkeycolumns fkc

    ON fk.FKeyID = fkc.FKeyID

    WHERE fkc.KeyNo = @column

    END

    --Create scripts for foreign keys

    SELECT DISTINCT

    'ALTER TABLE [' + RTRIM(TabOwner) + '].[' + RTRIM(TabName) + '] WITH NOCHECK ADD CONSTRAINT ['

    + RTRIM(FKeyName) + '] FOREIGN KEY (' + Columns + ') REFERENCES ['

    + RTRIM(RTabOwner) + '].[' + RTRIM(RTabName) + '] (' + RColumns + ')'

    + CHAR(10) + 'GO'

    FROM #foreignkeys

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply