SQL SERVER – Generate Foreign Key Scripts For Database

Regular reader of SQLAuthority.com blog Madhaiyan Seenivasan has send email with one very interesting script. This script generates all the foreign key addition script for your database. Many times there are situations where one need to drop all the foreign key and add them back. This SQL Script can be used for the same purpose.

You can execute the SP by executing its name like

EXEC DBO.SPGetForeignKeyInfo
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[SPGetForeignKeyInfo]')
AND
OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SPGetForeignKeyInfo
GO
CREATE PROCEDURE DBO.SPGetForeignKeyInfo
AS
/*
Author : Seenivasan
This procedure is used for Generating Foreign Key script.
*/
SET NOCOUNT ON
DECLARE
@FKName NVARCHAR(128)
DECLARE @FKColumnName NVARCHAR(128)
DECLARE @PKColumnName NVARCHAR(128)
DECLARE @fTableName NVARCHAR(128)
DECLARE @fUpdateRule INT
DECLARE
@fDeleteRule INT
DECLARE
@FieldNames NVARCHAR(500)
CREATE TABLE #Temp(
PKTABLE_QUALIFIER NVARCHAR(128),
PKTABLE_OWNER NVARCHAR(128),
PKTABLE_NAME NVARCHAR(128),
PKCOLUMN_NAME NVARCHAR(128),
FKTABLE_QUALIFIER NVARCHAR(128),
FKTABLE_OWNER NVARCHAR(128),
FKTABLE_NAME NVARCHAR(128),
FKCOLUMN_NAME NVARCHAR(128),
KEY_SEQ INT,
UPDATE_RULE INT,
DELETE_RULE INT,
FK_NAME NVARCHAR(128),
PK_NAME NVARCHAR(128),
DEFERRABILITY INT)
DECLARE TTableNames CURSOR FOR
SELECT
name
FROM sysobjects
WHERE xtype = 'U'
OPEN TTableNames
FETCH NEXT
FROM TTableNames
INTO @fTableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT
#Temp
EXEC dbo.sp_fkeys @fTableName
FETCH NEXT
FROM TTableNames
INTO @fTableName
END
CLOSE
TTableNames
DEALLOCATE TTableNames
SET @FieldNames = ''
SET @fTableName = ''
SELECT DISTINCT FK_NAME AS FKName,FKTABLE_NAME AS FTName,
@FieldNames AS FTFields,PKTABLE_NAME AS STName,
@FieldNames AS STFields,@FieldNames AS FKType
INTO #Temp1
FROM #Temp
ORDER BY FK_NAME,FKTABLE_NAME,PKTABLE_NAME
DECLARE FK_CUSROR CURSOR FOR
SELECT
FKName
FROM #Temp1
OPEN FK_CUSROR
FETCH
FROM
FK_CUSROR INTO @FKName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE
FK_FIELDS_CUSROR CURSOR FOR
SELECT
FKCOLUMN_NAME,PKCOLUMN_NAME,UPDATE_RULE,DELETE_RULE
FROM #TEMP
WHERE FK_NAME = @FKName
ORDER BY KEY_SEQ
OPEN FK_FIELDS_CUSROR
FETCH
FROM
FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
@fUpdateRule,@fDeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE
#Temp1 SET FTFields = CASE WHEN LEN(FTFields)
= 0 THEN '['+@FKColumnName+']'
ELSE FTFields
+',['+@FKColumnName+']' END
WHERE
FKName = @FKName
UPDATE #Temp1 SET STFields = CASE WHEN LEN(STFields)
= 0 THEN '['+@PKColumnName+']'
ELSE STFields
+',['+@PKColumnName+']' END
WHERE
FKName = @FKName
FETCH NEXT
FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
@fUpdateRule,@fDeleteRule
END
UPDATE
#Temp1 SET FKType = CASE WHEN @fUpdateRule = 0
THEN FKType + ' ON UPDATE CASCADE'
ELSE FKType END
WHERE
FKName = @FKName
UPDATE #Temp1 SET FKType = CASE WHEN @fDeleteRule = 0
THEN FKType + ' ON DELETE CASCADE'
ELSE FKType END
WHERE
FKName = @FKName
CLOSE FK_FIELDS_CUSROR
DEALLOCATE FK_FIELDS_CUSROR
FETCH next
FROM FK_CUSROR INTO @FKName
END
CLOSE
FK_CUSROR
DEALLOCATE FK_CUSROR
SELECT 'ALTER TABLE [dbo].['+FTName+'] ADD
CONSTRAINT ['
+FKName+'] FOREIGN KEY ('+FTFields+')
REFERENCES ['
+STName+'] ('+STFields+') '+FKType
FROM #Temp1
SET NOCOUNT OFF
RETURN
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com) , Madhaiyan Seenivasan

About these ads

14 thoughts on “SQL SERVER – Generate Foreign Key Scripts For Database

  1. so how come he says cusror instead of cursor? What if I just want to show all the tables with FKs and the FK names and the status if they are enabled or disabled?

  2. hello friendz !!

    I would like to know about foreign key reference in same database, my problem is –

    I have 2 tables
    table 1 –
    CREATE TABLE [dbo].[Increment](
    [Org_Value_ID] [numeric](9, 0) NOT NULL,
    [Ph_Code] [numeric](9, 0) NOT NULL,
    [Sr_No] [int] NOT NULL,
    [Increment_S] [numeric](15,2) NULL,
    [Increment_V] [numeric](15,2) NULL,
    CONSTRAINT [PK_Increment] PRIMARY KEY CLUSTERED
    (
    [Org_Value_ID] ASC,
    [Ph_Code] ASC,
    [Sr_No] ASC
    )) ON [PRIMARY]

    Table 2 –
    CREATE TABLE [dbo].[Org_Value](
    [Org_Value_ID] [numeric](9, 0) NOT NULL,
    [Ph_Code] [numeric](9, 0) NOT NULL,
    [Entitled] [numeric](9, 2) NULL,
    CONSTRAINT [PK_Org_Value] PRIMARY KEY CLUSTERED
    (
    [Org_Value_ID] ASC,
    [Ph_Code] ASC
    )) ON [PRIMARY]

    I want to add foreign key reference from Org_value to Increment on Org_value_id & Ph_Code

    when i tried –
    ALTER TABLE [dbo].[Org_Value] WITH CHECK ADD CONSTRAINT [FK_Org_Value_Increment] FOREIGN KEY([Org_value_ID], [Ph_Code]) REFERENCES [dbo].[Increment] ([Org_Value_ID],[Ph_Code])

    I am getting error -
    There are no primary or candidate keys in the referenced table ‘dbo.Increment’ that match the referencing column list in the foreign key ‘FK_Org_Value_Increment’.

    Please provide some comments, what should i do ?

  3. select ‘ALTER TABLE ‘+object_name(a.parent_object_id)+
    case when a.is_not_trusted = 1 then ‘ WITH NOCHECK ‘
    else ” end +
    ‘ ADD CONSTRAINT ‘+ a.name +
    ‘ FOREIGN KEY (‘ + c.name + ‘) REFERENCES ‘ +
    object_name(b.referenced_object_id) +
    ‘ (‘ + d.name + ‘)’
    from sys.foreign_keys a
    join sys.foreign_key_columns b
    on a.object_id=b.constraint_object_id
    join sys.columns c
    on b.parent_column_id = c.column_id
    and a.parent_object_id=c.object_id
    join sys.columns d
    on b.referenced_column_id = d.column_id
    and a.referenced_object_id = d.object_id
    where object_name(b.referenced_object_id) in
    (select name from sys.tables)
    order by c.name

  4. Hey man…I appreciate this posting.

    It saved me a LOT of time and headache. A lot better than using the generate sql script wizard.

    Thanks again.

  5. I like this script and can see where it can save me a lot of time. However I can not get it to work on any schema other then dbo. What change can I make to use scheams?

  6. My version based off raknel’s above formats the sql like MSMSS does. Good day!

    SELECT
    ‘ALTER TABLE dbo.[' + object_name(fKeys.parent_object_id) + ']‘
    + CASE WHEN fKeys.is_not_trusted = 1
    THEN ‘ WITH NOCHECK’
    ELSE ”
    END
    + ‘ ADD CONSTRAINT [' + fKeys.name + ']‘
    + ‘ FOREIGN KEY’ + char(13)
    + ‘(‘
    + LEFT(
    (
    SELECT pCols.name + ‘, ‘ AS [text()]
    FROM sys.foreign_key_columns fKeyCols
    INNER JOIN sys.columns pCols ON fKeyCols.referenced_column_id = pCols.column_id AND fKeyCols.referenced_object_id = pCols.object_id
    WHERE fKeyCols.constraint_object_id = fKeys.object_id
    ORDER BY fKeyCols.referenced_column_id
    FOR XML PATH(”)
    ),
    LEN(
    (
    SELECT pCols.name + ‘, ‘ AS [text()]
    FROM sys.foreign_key_columns fKeyCols
    INNER JOIN sys.columns pCols ON fKeyCols.referenced_column_id = pCols.column_id AND fKeyCols.referenced_object_id = pCols.object_id
    WHERE fKeyCols.constraint_object_id = fKeys.object_id
    ORDER BY fKeyCols.referenced_column_id
    FOR XML PATH(”)
    )
    ) -1
    )
    + ‘) REFERENCES ‘ + object_name(fKeys.referenced_object_id) + char(13)
    + ‘(‘
    + LEFT(
    (
    SELECT rCols.name + ‘, ‘ AS [text()]
    FROM sys.foreign_key_columns fKeyCols
    INNER JOIN sys.columns rCols ON fKeyCols.parent_column_id = rCols.column_id AND fKeyCols.parent_object_id = rCols.object_id
    WHERE fKeyCols.constraint_object_id = fKeys.object_id
    ORDER BY fKeyCols.referenced_column_id
    FOR XML PATH(”)
    ),
    LEN(
    (
    SELECT rCols.name + ‘, ‘ AS [text()]
    FROM sys.foreign_key_columns fKeyCols
    INNER JOIN sys.columns rCols ON fKeyCols.parent_column_id = rCols.column_id AND fKeyCols.parent_object_id = rCols.object_id
    WHERE fKeyCols.constraint_object_id = fKeys.object_id
    ORDER BY fKeyCols.referenced_column_id
    FOR XML PATH(”)
    )
    ) -1
    )
    + ‘)’ + char(13)
    + ‘GO’
    FROM
    (
    SELECT DISTINCT referenced_object_id, parent_object_id, object_id, name, is_not_trusted
    FROM sys.foreign_keys fKeys
    WHERE object_name(fKeys.referenced_object_id) = ‘BOOKINGLINEISSUE’
    ) AS fKeys

    • One more thing about my code… you have to output the results to text and then you have good formatting too.
      Ow also thanks for the initial post. :)

  7. Thanks for posting this. Previously, I used a simpler script, but it does not handle foreign keys with multiple columns. I was dreading the more complex coding. This is being done to programatically accomplish the FK drops and creates during sliding window partition maintenance.

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

  9. I know this is an old post, but for anyone else who regularly finds good SQL nuggets on this site and needs to be able to use this script with a different schema, all you need to do is:

    1 – Modify the “EXEC dbo.sp_fkeys @fTableName” line to instead read “EXEC dbo.sp_fkeys @pktable_name = @fTableName,@pktable_owner = N’myschema’” (replace myschema with your schema name)
    2 – Modify the “ALTER TABLE” lines to use your schema name in front of both the table name and the “REFERENCES” table name. (This assumes that all your pk and fk tables are in the same schema of course)

    A bit of a manual effort, but it worked like a charm for me.

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