Generate script that create and drop foreign keys references specific table

PROBLEM:

You want to generate script that create and drop foreign keys that references to specific table

SOLUTION:

To generate script that create foreign keys references specific table (change the name 'MY_REFERENCED_TABLE' to your table name)

WITH TABLESWITHFOREIGNKEY AS
  ( SELECT DISTINCT T.NAME AS TABLEWITHFOREIGNKEYNAME,
                    O.NAME AS FOREIGNKEYNAME,
--sub query with a comma separated resultset that returns the foreign key columns
   SUBSTRING (
     ( SELECT ',' + A.NAME
       FROM SYS.FOREIGN_KEY_COLUMNS
       INNER JOIN SYS.COLUMNS AS A ON PARENT_OBJECT_ID = A.OBJECT_ID
       AND PARENT_COLUMN_ID = A.COLUMN_ID
       WHERE PARENT_OBJECT_ID = T.OBJECT_ID
       AND CONSTRAINT_OBJECT_ID = O.OBJECT_ID
       ORDER BY CONSTRAINT_COLUMN_ID
       FOR XML PATH('') ), 2, 1000000) AS FOREIGNKEYCOLUMNS,
--sub query with a comma separated resultset that returns the referenced columns
   SUBSTRING (
      ( SELECT ',' + A.NAME
        FROM SYS.FOREIGN_KEY_COLUMNS
        INNER JOIN SYS.COLUMNS AS A ON REFERENCED_OBJECT_ID = A.OBJECT_ID
        AND REFERENCED_COLUMN_ID = A.COLUMN_ID
        WHERE PARENT_OBJECT_ID = T.OBJECT_ID
        AND CONSTRAINT_OBJECT_ID = O.OBJECT_ID
        ORDER BY CONSTRAINT_COLUMN_ID
        FOR XML PATH('') ), 2, 1000000) AS REFERENCESCOLUMNS
   FROM SYS.FOREIGN_KEY_COLUMNS AS FK
   INNER JOIN SYS.TABLES AS T ON FK.PARENT_OBJECT_ID = T.OBJECT_ID
   INNER JOIN SYS.OBJECTS AS O ON FK.CONSTRAINT_OBJECT_ID = O.OBJECT_ID
   INNER JOIN SYS.COLUMNS AS C ON FK.PARENT_OBJECT_ID = C.OBJECT_ID
   AND FK.PARENT_COLUMN_ID = C.COLUMN_ID
   WHERE FK.REFERENCED_OBJECT_ID =
       (SELECT OBJECT_ID
        FROM SYS.TABLES
        WHERE NAME = 'MY_REFERENCED_TABLE'))
SELECT ' IF NOT EXISTS
  (SELECT *
   FROM sys.foreign_keys
   WHERE object_id = OBJECT_ID(N''[' + FOREIGNKEYNAME + ']'') AND parent_object_id = OBJECT_ID(N''[' + TABLEWITHFOREIGNKEYNAME + ']''))
ALTER TABLE [' + TABLEWITHFOREIGNKEYNAME + '] WITH CHECK ADD CONSTRAINT [' + FOREIGNKEYNAME + '] FOREIGN KEY([' + FOREIGNKEYCOLUMNS + ']) REFERENCES [MY_REFERENCED_TABLE] ([' + REFERENCESCOLUMNS + ']) '
FROM TABLESWITHFOREIGNKEY
ORDER BY TABLEWITHFOREIGNKEYNAME

To generate script that drop foreign keys references specific table (change the name 'MY_REFERENCED_TABLE' to your table name)

WITH TABLESWITHFOREIGNKEY AS
  ( SELECT T.NAME AS TABLEWITHFOREIGNKEYNAME,
           O.NAME AS FOREIGNKEYNAME
   FROM SYS.FOREIGN_KEY_COLUMNS AS FK
   INNER JOIN SYS.TABLES AS T ON FK.PARENT_OBJECT_ID = T.OBJECT_ID
   INNER JOIN SYS.OBJECTS AS O ON FK.CONSTRAINT_OBJECT_ID = O.OBJECT_ID
   WHERE FK.REFERENCED_OBJECT_ID =
       (SELECT OBJECT_ID
        FROM SYS.TABLES
        WHERE NAME = 'MY_REFERENCED_TABLE'))
SELECT ' IF EXISTS
  (SELECT *
   FROM sys.foreign_keys
   WHERE object_id = OBJECT_ID(N''[' + FOREIGNKEYNAME + ']'') AND parent_object_id = OBJECT_ID(N''[' + TABLEWITHFOREIGNKEYNAME + ']''))
ALTER TABLE [' + TABLEWITHFOREIGNKEYNAME + '] DROP CONSTRAINT [' + FOREIGNKEYNAME + '] '
FROM TABLESWITHFOREIGNKEY
ORDER BY TABLEWITHFOREIGNKEYNAME

Check the scripts: create 2 tables with FK and run the scripts above. In this case, change MY_REFERENCED_TABLE to AAA

CONSTRAINT PK_1 PRIMARY KEY CLUSTERED (ID_AAA1,ID_AAA2))

CREATE TABLE BBB ( ID_BBB1 INT, ID_BBB2 INT, NAME NVARCHAR(50), CONSTRAINT PK_2 PRIMARY KEY CLUSTERED (ID_BBB1,ID_BBB2))

ALTER TABLE [dbo].[BBB]  WITH CHECK ADD  CONSTRAINT [FK_1] FOREIGN KEY([ID_BBB1],[ID_BBB2]) REFERENCES [dbo].[AAA] ([ID_AAA1],[ID_AAA2])

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!