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.

According to your title you want to remove foreign keys. But your last question ask for a "clear everything", including tables, stored procedures, functions. What do you mean with that? — Yaroslav, Oct 04 '12 at 06:59
@Yaroslav Thank you for point out. I am full-load with tasks. I just update my question details to match it with the title. — Anonymous, Oct 04 '12 at 07:20
Although I am sure, you must have checked for similar questions, but I would request you to revisit the solution here : http://stackoverflow.com/a/1438933/1268844 — Anshu, Oct 04 '12 at 07:03
It yields same result as my query. "Msg 2801, Level 16, State 1, Procedure XXX, Line 31 The definition of object 'XXX' has changed since it was compiled." — Anonymous, Oct 04 '12 at 07:25

score 38 · Answer 1 · edited May 23 '17 at 12:34

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

You should use `varchar(max)` and `'ALTER TABLE ' + QUOTENAME(FK.TABLE_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME) + '` to avoid syntax errors. — Antoine Aubry, Jan 22 '16 at 09:59

score 34 · Answer 2 · edited Apr 09 '21 at 03:49

34

Here's a short and sweet script I use (on SQL Server 2008 and up) to remove all foreign keys that also takes into account the object's schema:

declare @sql nvarchar(max) = (
    select 
        'alter table ' + quotename(schema_name(schema_id)) + '.' +
        quotename(object_name(parent_object_id)) +
        ' drop constraint '+quotename(name) + ';'
    from sys.foreign_keys
    for xml path('')
);
exec sp_executesql @sql;

edited Apr 09 '21 at 03:49

Max Szczurek

4,324
2
20
32

answered Oct 12 '16 at 18:50

arcain

14,920
6
55
75

2

This worked great. Note that on SQL Server Express Edition you need to define your parameter as `nvarchar`, using `varchar` will throw the following error: `Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.` This may be true of some of the newest SQL Server versions as well, but I'm unable to test. – CJ Edgerton Apr 22 '20 at 19:37

score 13 · Answer 3 · answered Nov 28 '14 at 23:22

I have improved the first script provided by @Yaroslav and the script provided by @Quandary so they now works for databases whose SQL query to drop all foreign keys one by one exceeds the size allocated for the SQL variable (4000 or MAX characters).

The altered scripts drop 5 foreign keys per iteration (just to be safe, implemented by adding TOP 5). The scripts stop when there is no foreign key left to drop (the SQL variable stays empty after running the SELECT).

First script by @Yaroslav

DECLARE @SQL varchar(4000)
IterationStart:
SET @SQL=''
SELECT TOP 5 @SQL = @SQL + 'ALTER TABLE ' + FK.TABLE_NAME + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
  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
IF @SQL <> ''
BEGIN
  EXEC(@SQL)
  GOTO IterationStart
END

Script by @Quandary

DECLARE @sql nvarchar(MAX) 

IterationStart:
SET @sql = '' 

SELECT TOP 5 @sql = @sql + 'ALTER TABLE ' + QUOTENAME(RC.CONSTRAINT_SCHEMA) 
    + '.' + QUOTENAME(KCU1.TABLE_NAME) 
    + ' DROP CONSTRAINT ' + QUOTENAME(rc.CONSTRAINT_NAME) + '; ' 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

IF @SQL <> ''
BEGIN
  EXEC(@SQL)
  GOTO IterationStart
END

I used the script by @Quandary in your answer and it worked perfectly. Kudos — mikaelovi, May 15 '20 at 11:22

Stefan Steiger · Answer 4 · 2019-04-03T14:43:51.433

Most simple variant:

DECLARE @sql nvarchar(MAX) 
SET @sql = N'' 

SELECT @sql = @sql + N'ALTER TABLE ' + QUOTENAME(KCU1.TABLE_SCHEMA) 
    + N'.' + QUOTENAME(KCU1.TABLE_NAME) 
    + N' DROP CONSTRAINT ' -- + QUOTENAME(rc.CONSTRAINT_SCHEMA)  + N'.'  -- not in MS-SQL
    + QUOTENAME(rc.CONSTRAINT_NAME) + N'; ' + CHAR(13) + CHAR(10) 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 


-- PRINT @sql 
EXECUTE(@sql)

score 3 · Answer 5 · answered May 06 '15 at 11:10

I used the INFORMATION_SCHEMA solution mentioned by @Yaroslav, but had too many foreign key constants in my database to fit them all into a varchar(MAX). So I had to modify the script to use a temporary table and a cursor instead.

Also, I added [] around the table name.

DECLARE @SQL TABLE (Command VARCHAR(MAX))

INSERT @SQL
SELECT 'ALTER TABLE [' + FK.TABLE_NAME + '] DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
  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

DECLARE cmdCursor CURSOR
    FOR SELECT Command FROM @SQL
OPEN cmdCursor
DECLARE @Command VARCHAR(MAX)

FETCH NEXT FROM cmdCursor INTO @Command
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Command
    EXEC (@Command)
    FETCH NEXT FROM cmdCursor INTO @Command
END

CLOSE cmdCursor;
DEALLOCATE cmdCursor;

score 2 · Answer 6 · answered Sep 11 '15 at 13:13

CREATE TABLE #Commands (Command VARCHAR(MAX))

INSERT #Commands
SELECT 'ALTER TABLE ' + QUOTENAME(RC.CONSTRAINT_SCHEMA)
    + '.' + QUOTENAME(KCU1.TABLE_NAME)
    + ' DROP CONSTRAINT ' + QUOTENAME(rc.CONSTRAINT_NAME) + '; '
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE ORDINAL_POSITION=1

--SELECT * FROM #Commands

DECLARE @Command VARCHAR(MAX)
DECLARE curCommand CURSOR FOR
SELECT Command FROM #Commands

OPEN curCommand

FETCH NEXT FROM curCommand INTO @Command

WHILE @@FETCH_STATUS =0
BEGIN

    EXEC(@Command)
    FETCH NEXT FROM curCommand INTO @Command

END

CLOSE curCommand
DEALLOCATE curCommand

DROP TABLE #Commands

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. — NathanOliver, Sep 11 '15 at 14:11
For databases with a lot of constraints other solutions mentioned failed but this one worked for me. — JCallico, Mar 02 '17 at 22:03

score 1 · Answer 7 · answered Jan 13 '20 at 22:28

See Pinal's answer from this link, it is very helpful

https://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-database-create-all-the-foreign-key-constraint-in-database/

SET NOCOUNT ON
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT [' + ForeignKeyConstraintName + ']

GO'
FROM
@table

How to drop all Foreign Key constraints in all tables?

7 Answers7

Linked