Journey to SQL Authority with Pinal Dave

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://www.SQLAuthority.com), Madhaiyan Seenivasan