Removing All Foreign Keys in Database in SQL Server

Hello everyone,

In this article, I will try to give information about removing all Foreign Keys in the database in SQL Server.

In SQL Server, in some cases, you may need to Remove All Foreign Keys in the database.

You can easily do this using the code below.

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;

When you run the above code, you will see a result similar to the one below.

As you can see, the code to Remove All Foreign Keys has been created. The rest is to copy these codes and run them in the relevant database.

Note: Do not try this operation on a live database. The responsibility lies with you.

Good luck to everyone in business and life.