Disable or Enable Constraint

Issue
As there are 3 database environments such as development, testing and production, sometimes there is a request to refresh master or transaction tables in testing from production with specific period.

1st issue is sometimes some data already exists in testing. So I need to delete the existing data. For deleting data we have to delete starting from detail table to master table.

2nd issue is when inserting data from production to testing, we need to insert data starting from master table to detail table. If we do not follow this rule, the action will be failed due to foreign key constraint. It is little bit take time for DBA to analysis which the master table or detail table. For data movement from one to another environment which have identical schema, the fastest way is disable the constraints during the data movement and enable it back after it is finish. Below script only valid for check and foreign key constraint.

Script

Disable Constraints All Tables

sp_msforeachtable @command1 = "print 'Disable constraint on ? '" , @command2 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

Enable Constraints All Tables

sp_msforeachtable @command1 = "print 'Enable constraint on ? '" , @command2 = "ALTER TABLE ? CHECK CONSTRAINT ALL"

After you move the data and enable the constraints, this enabling constraint doesn’t check the data that already exists in the table. So you will not receive any violation message though there is a violated data. Use DBCC CHECKCONSTRAINTS to check whether there is a violated data or not and just clean it manually.

Since the script enable/disable all the constraints and triggers, just make sure before you run the script, note the constraint already disabled before you want to enable all the constraints vice versa by running below script.

List All Constraints Status 

SELECT CASE WHEN OBJECTPROPERTY(constid, 'cnstisdisabled') = 0 THEN 'Enabled' ELSE 'Disabled' END AS Status ,O1.Name AS ConstraintName ,O2.Name AS TableName ,C1.name AS ColumnName ,O3.name AS ReferencedTable ,C2.name AS ReferencedColumn FROM sysforeignkeys FK INNER JOIN sysobjects O1 ON O1.id = FK.constid INNER JOIN sysobjects O2 ON O2.id = FK.fkeyid INNER JOIN syscolumns C1 ON C1.id = FK.fkeyid AND C1.colid = FK.fkey INNER JOIN sysobjects O3 ON O3.id = FK.rkeyid INNER JOIN syscolumns C2 ON C2.id = FK.rkeyid AND C2.colid = FK.rkey ORDER BY TableName, ConstraintName, FK.KeyNo