SQL: Script to Drop All Foreign Key Constraints from the Current Database

If you need to drop all foreign key constraints from the current SQL Server database, you can generate the required ALTER TABLE statements using the following query.

Generate the Script

SELECT
    'ALTER TABLE ' + OBJECT_NAME(F.parent_object_id)
    + ' DROP CONSTRAINT ' + F.name
FROM sys.foreign_keys F;

Run the above query. It will generate ALTER TABLE statements for all foreign key constraints in the current database.

Example Output

ALTER TABLE BR_DTL DROP CONSTRAINT FK__BR_DTL__BR_PTCD
ALTER TABLE BR_DTL DROP CONSTRAINT FK__BR_DTL__BR_TNID
ALTER TABLE COM_MST DROP CONSTRAINT FK__COM_MST__COM_TPC

Copy the generated statements and run them to drop the foreign key constraints. This script can be useful when you need to temporarily remove foreign key constraints, such as during database maintenance, data migration, or when you need to modify and recreate constraints.

I came across this requirement while working on a project when my Project Manager asked me to find a way to drop all constraints and recreate them later. Working through the problem was a good database-learning experience and helped me understand how SQL Server stores and exposes foreign key constraint information.