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.

Removing All Foreign Keys in Database in SQL Server

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.

108 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!