SQL Script to Drop and Re-create All Foreign Keys in SQL Server

Introduction

This SQL script will generate a script to drop and create all foreign keys in database.

Background

I had to load some database with fresh data every few days for testing purposes and some reporting purposes. I have an SSIS package to load the data from source database but one issue I faced is I could not truncate the tables because of foreign keys. So, initially I started with using DELETE, but which was taking some time. So to improve the performance and get rid of all foreign keys before start of the load and then re-create them after load is finished, I have written one interesting script as below. This script gives DROP and CREATE statement for all foreign keys which I was running in my package.

Code

WITH RefColumns AS
(
       SELECT
              C.referenced_object_id AS [object_id],
              C.parent_object_id,
              STUFF((SELECT ', ' + QUOTENAME(B.name)
                     FROM sys.foreign_key_columns A 
                           JOIN sys.columns B ON B.[object_id] = _
                           A.referenced_object_id AND B.column_id = A.referenced_column_id
                           WHERE C.parent_object_id = A.parent_object_id AND _
                           C.referenced_object_id = A.referenced_object_id
                           FOR XML PATH('')), 1, 2, '') AS ColumnNames
       FROM sys.foreign_key_columns C
       GROUP BY C.referenced_object_id, C.parent_object_id
)
,ParentColumns AS
(
       SELECT
              C.parent_object_id AS [object_id],
              C.referenced_object_id,
              STUFF((SELECT ', ' + QUOTENAME(B.name)
                     FROM sys.foreign_key_columns A 
                           JOIN sys.columns B ON B.[object_id] = _
                           A.parent_object_id AND B.column_id = A.parent_column_id
                           WHERE C.parent_object_id = A.parent_object_id AND _
                           C.referenced_object_id = A.referenced_object_id
                           FOR XML PATH('')), 1, 2, '') AS ColumnNames
       FROM sys.foreign_key_columns C
       GROUP BY C.parent_object_id, C.referenced_object_id
)
 
SELECT
       'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + _
       QUOTENAME(PT.name) + ' DROP  CONSTRAINT' + ' ' + QUOTENAME(FK.name) AS [DropFKScript],
       'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + _
       QUOTENAME(PT.name) + ' WITH CHECK ADD  CONSTRAINT '+ QUOTENAME(FK.name) + CHAR(13) + CHAR(10) +
       'FOREIGN KEY(' + PC.ColumnNames + ')' + CHAR(13) + CHAR(10) +
       'REFERENCES ' + QUOTENAME(SCHEMA_NAME(RT.[schema_id])) + '.' + _
       QUOTENAME(RT.name) + ' (' + RC.ColumnNames + ')' + CHAR(13) + _
       CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +
       'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + _
       QUOTENAME(PT.name) + ' CHECK CONSTRAINT ' + QUOTENAME(FK.name) + _
       CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
       AS [CreateFKScript]
FROM sys.foreign_keys FK   
       JOIN sys.tables PT ON PT.[object_id] = FK.parent_object_id
       JOIN ParentColumns AS PC ON PC.[object_id] = FK.parent_object_id _
       AND PC.referenced_object_id = FK.referenced_object_id
       JOIN sys.tables RT ON RT.[object_id] = FK.referenced_object_id
       JOIN RefColumns AS RC ON RC.[object_id] = FK.referenced_object_id _
       AND RC.parent_object_id = FK.parent_object_id
WHERE PT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
       AND RT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
ORDER BY PT.name
GO 

I have excluded dtproperties, sysdiagrams and __RefactorLog table using name because SQL server does not provide any flag to exclude these tables. These tables are created as user tables but somehow SQL server internally identifies them as system table. I think they have also hard coded these names or at least not disclosed how to ignore them from user table list. If anyone can find out a way to ignore these tables from user table list, then please post your answer.

You can also visit my blog at http://sql31.blogspot.co.uk/.

History

• 07-Mar-2014: First version posted