How to Generate Script To Enable All Foreign Key Constraints in SQL Server Database - SQL Server / TSQL Tutorial Part 78

Scenario: 

You are working as SQL Server developer, You need to prepare scripts to enable all the Foreign Key Constraint in a database which are disabled. This could happen, maybe somebody has disabled Foreign Key Constraints for a data load where they don't care about violation of referential integrity and forgot to enable them later.

Solution:

First of all let's get the list of Foreign Key Constraints with status by using below query

--Get List of Foreign Key Constraints if Enabled or Disabled
    USE YourDatabaseName
    GO
    Select 
    Schema_name(Schema_id) as SchemaName,
    object_name(Parent_object_id) as TableName,
    name as ForeignKeyConstraintName,
    Case When Is_disabled=1 Then 'No'
    ELSE 'Yes' End as IsEnabled
    from sys.foreign_keys

How to check if Foreign Key Constraint is Disabled or Enabled in SQL Server Database

Now let's generate scripts to enable the Foreign Key Constraints which are disabled in SQL Server Database by using below query.

USE YourdatabaseName
go
-- Enable Foreign Key Constraints Script 
SELECT distinct 'ALTER TABLE ' 
+ '['+ Schema_name(FK.schema_id) 
+ '].['+ OBJECT_NAME(FK.parent_object_id) 
+ ']'+ ' CHECK  CONSTRAINT ' 
+ '[' + FK.name + ']' AS EnableConstraintQuery
 FROM   sys.foreign_keys AS FK
 where is_disabled=1

How to generate script to enable all Foreign Key Constraints in SQL Server Database 

Copy the results and run in SSMS to enable disabled Foreign Key Constraints in SQL Server Database.

Video Demo : How to generate script to enable all Foreign Key Constraint in SQL Server