Script for all Foreign Keys of all tables of a DB

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

  • 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

  • Hey Neon,

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

  • 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

    SELECTFKeyID = 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

    SETColumns = '[' + 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

    SETColumns = 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 3 (of 3 total)

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