Generate Commands to Create and Drop FKs

DROP PROCEDURE dbo.usp_GenerateCreateDropFKCommands

GO

CREATE PROCEDURE dbo.usp_GenerateCreateDropFKCommands(@TableName SYSNAME = NULL)

AS

/***************************************************************

Purpose: To generate commands to create or drop all FKs

No changes are made by this storted procedure

Author: ChillyDBA

History: 5 Jul 2012

****************************************************************/

CREATE TABLE #FKs

(

DatabaseName SYSNAME NULL,

FK_TableSchema SYSNAME NULL,

FK_TableName SYSNAME NULL,

FK_Schema SYSNAME NULL,

FK_Name SYSNAME NULL,

FK_ColumnName SYSNAME NULL,

FK_ColumnOrder INT,

PK_TableSchema SYSNAME NULL,

PK_TableName SYSNAME NULL,

PK_ColumnName SYSNAME NULL

)

CREATE TABLE #DropCommands

(

CommandID INT IDENTITY(1,1),

Command VARCHAR(8000)

)

CREATE TABLE #CreateCommands

(

CommandID INT IDENTITY(1,1),

Command VARCHAR(8000)

)

DECLARE

@Command VARCHAR(8000),

@FK_Name SYSNAME,

@FK_Columns VARCHAR(8000),

@PK_Columns VARCHAR(8000)

-- retrieve all FK information for the database

INSERT #FKs

(

DatabaseName,

FK_TableSchema,

FK_TableName,

FK_Schema,

FK_Name,

FK_ColumnName,

FK_ColumnOrder,

PK_TableSchema,

PK_TableName,

PK_ColumnName

)

SELECT

DatabaseName,

FK_TableSchema,

FK_TableName,

FK_Schema,

FK_Name,

FK_ColumnName,

FK_ColumnOrder,

PK_TableSchema,

PK_TableName,

PK_ColumnName

FROM dbo.udf_GetForeignKeyColumns(@TableName) fkc

-- loop through all FK details to create the commands

SELECT @FK_Name = MIN(FK_Name)

FROM #FKs

WHILE @FK_Name IS NOT NULL

BEGIN

SELECT

@Command = '',

@FK_Columns = NULL,

@PK_Columns = NULL

-- a useful technique to unpivot a list of columns

-- into a comma-separated list with no trailing comma

SELECT @FK_Columns = COALESCE (@FK_Columns + ', ', '') + '[' + FK_ColumnName + '] '

FROM #FKs

WHERE FK_Name = @FK_Name

ORDER BY FK_ColumnOrder

SELECT @PK_Columns = COALESCE (@PK_Columns + ', ', '') + '[' + PK_ColumnName + '] '

FROM #FKs

WHERE FK_Name = @FK_Name

ORDER BY FK_ColumnOrder

-- create the DROP FK command

SELECT @Command = @Command

+ 'ALTER TABLE ['

+ FK_TableSchema

+ '].['

+ FK_TableName

+ '] DROP CONSTRAINT ['

+ FK_Name

+ ']'

FROM #FKs

WHERE FK_Name = @FK_Name

AND FK_ColumnOrder = 1 -- saves using a messy set of MAX() commands - the FK will always have a columns #1

INSERT #DropCommands(Command)

VALUES (@Command)

-- good practice to zero the variable as is the results of next query are NULL,

-- then the old value (drop command) will remain in the variable

SELECT @Command = ''

-- create the CREATE FK command

SELECT @Command = @Command

+ 'ALTER TABLE ['

+ FK_TableSchema

+ '].['

+ FK_TableName

+ '] WITH CHECK ADD CONSTRAINT ['

+ FK_Name

+ '] FOREIGN KEY'

+ ' ('

+ @FK_Columns

+ ') REFERENCES ['

+ PK_TableSchema

+ '].['

+ PK_TableName

+ '] ('

+ @PK_Columns

+ ')'

FROM #FKs

WHERE FK_Name = @FK_Name

AND FK_ColumnOrder = 1 -- saves using a messy set of MAX() commands - the FK will always have a columns #1

INSERT #CreateCommands(Command)

VALUES (@Command)

SELECT @FK_Name = MIN(FK_Name)

FROM #FKs

WHERE FK_Name > @FK_Name

END

SELECT Command AS 'Commands to DROP FKs'

FROM #DropCommands

ORDER BY CommandID

SELECT Command AS 'Commands to CREATE FKs'

FROM #CreateCommands

ORDER BY CommandID

DROP TABLE #DropCommands

DROP TABLE #CreateCommands

DROP TABLE #FKs