Generate DROP/CREATE statements for all referencing foreign keys

This is a quick post to generate the DROP/CREATE statements for all foreign keys referencing a given primary key... like the subject says. 😉  In this example, we generate the statements for all foreign keys referencing PK_Address_AddressID on Person.Address in AdventureWorks2012, and this is the output:

See this post for a little more detail on when you might need to do this.

     CREATE TABLE #ForeignKeys (

       SchemaName VARCHAR(128),

       TableName VARCHAR(128),

       ForeignKeyName VARCHAR(128),

       DropScript VARCHAR(MAX),

       CreateScript VARCHAR(MAX)

     );

     DECLARE @PKname NVARCHAR(128) = 'PK_Address_AddressID' --<-- change this to your PK name

     DECLARE @create NVARCHAR(MAX) = '';

     -- populate the drop statement

     INSERT #ForeignKeys (SchemaName,TableName,ForeignKeyName,DropScript,CreateScript)

     SELECT schemas.[name],

       tables.[name],

       foreign_keys.[name],

       'ALTER TABLE ' + QUOTENAME(schemas.[name]) + '.' + QUOTENAME(tables.[name]) + ' DROP CONSTRAINT    

             ' + QUOTENAME(foreign_keys.[name]),

       NULL

     FROM

sys.foreign_keys INNER JOIN sys.tables

         ON foreign_keys.parent_object_id = tables.object_id INNER JOIN sys.schemas

           ON tables.schema_id = schemas.schema_id INNER JOIN sys.indexes

                ON sys.indexes.object_id = sys.foreign_keys.referenced_object_id

                AND sys.indexes.index_id = sys.foreign_keys.key_index_id

     WHERE

       indexes.name = @PKname;

     -- populate the create statement

     UPDATE #ForeignKeys

     SET CreateScript = Creation.Script

     FROM #ForeignKeys INNER JOIN

     (

     SELECT

        cs.[name] SchemaName,

        ct.[name] TableName,

        foreign_keys.[name] ForeignKeyName,

        'ALTER TABLE ' + QUOTENAME(cs.[name]) + '.' + QUOTENAME(ct.[name]) + ' ADD CONSTRAINT ' + QUOTENAME(foreign_keys.[name]) + ' FOREIGN KEY (' +

        STUFF((

              SELECT ',' + QUOTENAME(columns.[name])

              FROM sys.columns INNER JOIN sys.foreign_key_columns

                ON foreign_key_columns.parent_column_id = columns.column_id

                AND foreign_key_columns.parent_object_id = columns.object_id

              WHERE

                     foreign_key_columns.constraint_object_id = foreign_keys.object_id

              ORDER BY

                     foreign_key_columns.constraint_column_id

       FOR XML PATH(''), TYPE

                       ).value('.1', 'VARCHAR(MAX)'), 1, 1,'') + ') REFERENCES ' + QUOTENAME(schemas.[name]) + '.' + QUOTENAME(tables.[name]) + '(' +

     STUFF((

     SELECT ',' + QUOTENAME(columns.[name])

     FROM sys.columns INNER JOIN sys.foreign_key_columns fkc

       ON fkc.referenced_column_id = columns.column_id

       AND fkc.referenced_object_id = columns.object_id

     WHERE

         fkc.constraint_object_id = foreign_keys.object_id

     ORDER BY

         fkc.constraint_column_id

      FOR XML PATH(''), TYPE

       ).value('.1', 'NVARCHAR(MAX)'), 1, 1, '') + ')' Script

     FROM sys.foreign_keys INNER JOIN sys.tables

       ON foreign_keys.referenced_object_id = tables.object_id INNER JOIN sys.schemas

         ON tables.schema_id = schemas.schema_id INNER JOIN sys.tables ct

           ON foreign_keys.parent_object_id = ct.object_id INNER JOIN sys.schemas cs

             ON ct.schema_id = cs.schema_id

     WHERE

         tables.is_ms_shipped = 0

         AND ct.is_ms_shipped = 0

     ) Creation

     ON Creation.SchemaName = #ForeignKeys.SchemaName

     AND Creation.TableName = #ForeignKeys.TableName

     AND Creation.ForeignKeyName = #ForeignKeys.ForeignKeyName

     -- output your datta

     SELECT

       SchemaName,

       TableName,

       ForeignKeyName,

       DropScript,

       CreateScript

     FROM

       #ForeignKeys

     -- drop your tmp table

     DROP TABLE #ForeignKeys