CODEWITHSUNDEEP
sqlsql-servert-sql
Elham Azadfar
Elham Azadfar

Reputation: 739

How to drop all foreign keys from a SQL Server database?

I want to drop the all foreign keys that have the following conditions.

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME IN ('Table1', 'Table2')
  AND CONSTRAINT_NAME LIKE '%FK__%__DL%'

Upvotes: 5

Views: 13242

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

Similar to what Siyavash suggested, but I prefer a bit more control over the process..

If you use this query:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.[', TABLE_NAME, '] DROP CONSTRAINT [', CONSTRAINT_NAME, ']')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

Run it; it generates a list of drop commands for every FK in the DB. You can then either pick out the ones you want to execute or you can add more clauses the the WHERE to restrict further

As with all "SQL that writes SQL" you run it by copying the results out of the grid, pasting into a new window in SSMS and running them (F5)

Hence, your query, with the CONCAT that writes the drop command:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.[', TABLE_NAME, '] DROP CONSTRAINT [', CONSTRAINT_NAME, ']')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME IN ('Table1', 'Table2')
  AND CONSTRAINT_NAME LIKE '%FK__%__DL%'

Upvotes: 1

Siyavash Hamdi
Siyavash Hamdi

Reputation: 3087

There is a table named INFORMATION_SCHEMA.TABLE_CONSTRAINTS which stores all tables constraints. constraint type of FOREIGN KEY is also keeps in that table. So by filtering of this type you can reach to all foreign keys.

SELECT  *
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'

If you create a dynamic query (for DROP-ing the foreign key) in order to alter the table, you can reach to the aim of altering the constraints of all tables.

WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME IN ('Table1', 'Table2') AND CONSTRAINT_NAME LIKE '%FK__%__DL%'))
BEGIN
    DECLARE @sql_alterTable_fk NVARCHAR(2000)

    SELECT  TOP 1 @sql_alterTable_fk = ('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'
            AND TABLE_NAME IN ('Table1', 'Table2')
            AND CONSTRAINT_NAME LIKE '%FK__%__DL%'

    EXEC (@sql_alterTable_fk)
END

EXISTS function with its parameter assures that there is at least one constrain for foreign key.

Upvotes: 8

Related Questions