How to drop all Foreign Key constraints in all tables?

Question

I want to write sql command to drop all constraints in all tables. I searched on the internet and found the following which works fine if the database is small and not complex.

DECLARE @name VARCHAR(128)  DECLARE @constraint VARCHAR(254)  DECLARE @SQL VARCHAR(254)  DECLARE @schema VARCHAR(128)  SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)  SELECT @schema = (SELECT TOP 1 schema_name(schema_id) FROM sys.objects WHERE [name] = @name)   WHILE @name is not null  BEGIN      SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)      WHILE @constraint IS NOT NULL      BEGIN          SELECT @SQL = 'ALTER TABLE ' + @schema + '.[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'          EXEC (@SQL)          PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name          SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)      END  SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)  SELECT @schema = (SELECT TOP 1 schema_name(schema_id) FROM sys.objects WHERE [name] = @name)  END  GO  

It does not work if I run it with a more complex database or even AdventureWork. It shows some erros like below.

Msg 3728, Level 16, State 1, Line 1 'FK_ap_invoice_modification_type_id' is not a constraint. Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. See previous errors. Msg 3725, Level 16, State 0, Line 1 The constraint 'PK_ap_invoice' is being referenced by table '_drop_now_ap_invoice_detail', foreign key constraint 'FK_ap_invoice_detail_ap_invoice'. Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. See previous errors. 

The reason is because some FKs are referenced by other table. I have to run this script for a couple times until the database is clean.

I want to know how can I clear all FKs in the database.

Answer 1

There is lot of information about the subject all around. Check this detailed answer by @AaronBertrand. It talks about temporary disabling the foreign keys but reading it all and modifying at will you will have a nice script to play with and achieve a lot.

From my side I can propose 2 different scripts to get all foreign keys. On both cases uncomment the --EXEC (@SQL) to execute your ALTER code. Or you can wait until it prints all the alter clauses and then copy paste to execute them.

First one uses the INFORMATION_SCHEMA to get the constraints:

DECLARE @SQL VARCHAR(MAX)='' SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13) --SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME   FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK     ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK     ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU     ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME  INNER JOIN (             SELECT i1.TABLE_NAME, i2.COLUMN_NAME               FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1              INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2                 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME             WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'            ) PT     ON PT.TABLE_NAME = PK.TABLE_NAME  --EXEC (@SQL)  PRINT @SQL 

This one using different system views and a CTE table.

DECLARE @SQL varchar(4000)='' ;WITH ReferencingFK AS  (     SELECT fk.Name AS 'FKName', OBJECT_NAME(fk.parent_object_id) 'ParentTable',             cpa.name 'ParentColumnName', OBJECT_NAME(fk.referenced_object_id) 'ReferencedTable',             cref.name 'ReferencedColumnName'     FROM sys.foreign_keys fk     INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id     INNER JOIN sys.columns cpa ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id     INNER JOIN sys.columns cref ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id ) SELECT @SQL = @SQL + 'ALTER TABLE ' + ParentTable + ' DROP CONSTRAINT [' + RTRIM(FKName) +'];' + CHAR(13) --SELECT FKName, ParentTable, ParentColumnName, ReferencedTable, ReferencedColumnName   FROM ReferencingFK  WHERE ReferencedTable = 'Employee'  ORDER BY ParentTable, ReferencedTable, FKName  --EXEC (@SQL)   PRINT @SQL