How to disable all Check Constraints in SQL Server Database - SQL Server / TSQL Tutorial Part 87

Scenario:

You are working as SQL Server / ETL developer. You need to load bunch of data to tables in SQL Server Database. Check Constraints are created on different tables. There are chances that the data you are going to load will not qualify according to Check Constraint. The business want you to load the data anyways even it does not qualify with Check Constraint. You want to temporary disable all the constraints in SQL Server database and then load the data and then re-enabled the Check Constraints.


Solution:

The below script can be used to generate Disable Check Constraint script for all the Check Constraints which are enabled in database. You can further filter the tables in where clause if you don't want to generate script for all the tables.


 --Generate Script to Disable 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) 
    + ']' + ' NOCHECK  CONSTRAINT ' 
    + '[' + NAME + ']' AS DisableCheckConstraint
 From sys.check_constraints
 where is_disabled=0




How to generate scripts to Disable all Check Constraints in SQL Server Database

Copy the results from DisableCheckConstraint column and run in SSMS to disable required Check Constraints.

ALTER TABLE [dbo].[Customer22] NOCHECK CONSTRAINT [CK__Customer2__FName__6C190EBB]

ALTER TABLE [dbo].[Employee] NOCHECK CONSTRAINT [CK__Employee__FName__7A672E12]


Video Demo : How to generate scripts to disable all Check Constraints in SQL Server

No comments:

Post a Comment