Friday, 21 February 2014

SQL Script to drop and re-create all foreign keys in SQL server

I had to load some database with fresh data every few days for testing purpose and some reporting purpose. I have a 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 key before start of the load and then re-create them after load is finish, I have written one interesting script as below. This script give DROP and CREATE statement for all foreign key which I was running in my package.

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

 



If anyone find any error or think this can be improved then please do post your comment.

4 comments:

  1. Very helpful. Saved me some nightly hours.
    Thank You.
    Clemens / Austria

    ReplyDelete
  2. Very Good Information. Thanks for this great share. The Script Component provides another area where programming logic can be applied in an SSIS package. This component, which can be used only in the Data Flow portion of an SSIS package, allows programmatic tasks to occur in the data stream. Each Script Task is called only once within a Control Flow, unless it is in a looping control. The Script Component has to be higher octane because it is going to be called per row of data in the data stream.
    Using the script component in SSIS

    ReplyDelete