Declarative Referential Integrity - Generate Commands to Create and Drop FKs

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        5 Jul 2012

Description

This SP makes use of the function to get foreign key column details.
 
Returns 2 lists of commands:
  1. Create all FKs in a database using the definitions already in place in the database
  2. Drop all FKs currently in place in the database
NOTE:  No changes are made to database structure when running this SP - it only generates TSQL.

Code

Stored Procedure:

 

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



Test Code:


USE AdventureWorksDW
GO

EXEC dbo.usp_GenerateCreateDropFKCommands NULL
EXEC dbo.usp_GenerateCreateDropFKCommands 'FactInternetSalesReason'


ċ
usp_GenerateCreateDropFKCommands.sql
(5k)
Andy Hughes,
Jul 5, 2012, 8:00 AM
Comments