How to Enable all Check Constraints in SQL Server Database - SQL Server / TSQL Tutorial Part 88

Scenario:

You are working as SQL Server developer or ETL developer, You disabled all the Check Constraints in SQL Server Database before loading data. You need to provide the script that should be able to generate the enable Check Constraint scripts for all the Check Constraints which are disabled in SQL Server Database.

Solution:

We can use system objects to generate enable Check Constraints scripts. Below query can be used to generate scripts to Enable Check Constraints, If you don't want to enable all Check Constraints you can further filter the objects in where clause.

 --Generate Script to Enable All Check Constraint in SQL Server Database
 Select DB_Name() AS DBName,
 Schema_Name(Schema_id) AS TableSchema,
 Object_name(parent_object_id) as TableName,
  definition,
  'Alter Table [' + Schema_Name(Schema_id) 
    + '].[' + Object_name(parent_object_id) 
    + ']' + ' CHECK  CONSTRAINT ' 
    + '[' + NAME + ']' AS EnableCheckConstraint
 From sys.check_constraints
 where is_disabled=1

How to generate Enable all Check Constraints Script in SQL Server Database

Copy the results from EnableCheckConstraint column, paste in SSMS and execute.

Alter Table [dbo].[Customer22] CHECK  CONSTRAINT [CK__Customer2__FName__6C190EBB]
Alter Table [dbo].[Employee] CHECK  CONSTRAINT [CK__Employee__FName__7A672E12]

Video Demo : How to generate Scripts to enable all Check Constraints

in SQL Server Database