Foreign keys are an essential component of relational databases. They establish relationships between tables and ensure data integrity. However, there are situations where you may need to drop and add foreign keys in your database. In this article, we will discuss a script that can generate all the foreign key addition scripts for your SQL Server database.

The script provided below can be executed as a stored procedure:

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 scripts.
*/
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

This script uses a temporary table to store the foreign key information. It then iterates through the tables in the database and retrieves the foreign key details using the sp_fkeys system stored procedure. The script then generates the ALTER TABLE statements to add the foreign keys back to the tables.

By executing this stored procedure, you can easily generate the foreign key addition scripts for your SQL Server database. This can be useful in scenarios where you need to recreate the foreign keys after dropping them.

Remember to always test any scripts on a non-production environment before applying them to your production database.

We hope you find this script helpful in managing foreign keys in your SQL Server database!