How to drop Foreign Key Constraints in SQL Server Database for all the tables - SQL Server / TSQL Tutorial Part 72

Scenario:

You are working as SQL Server developer, you are asked to provide the scripts those can be used to drop Foreign Key Constraints on all the tables in a database if exists.

Solution:

We can use system view to generate the drop Foreign Key Constraints for all the tables in SQL Server Database by using below query.

USE YourdatabaseName
go
-- Drop Foreign Key Constraints Script 
SELECT distinct 'ALTER TABLE ' 
+ '['+ Schema_name(FK.schema_id) 
+ '].['+ OBJECT_NAME(FK.parent_object_id) 
+ ']'+ ' DROP  CONSTRAINT ' 
+ '[' + FK.name + ']' AS DropConstraintQuery
 FROM   sys.foreign_keys AS FK

How to drop all the Foreign Key Constraints in SQL Server Database

Execute above query and then take the results and run in SSMS to drop all the Foreign Key Constraint in database.

Video Demo : How to generate drop Foreign Key Constraint scripts for entire database in SQL Server