SQL SERVER – How to Disable and Enable All Constraint for Table and Database

One of the most popular questions I find still coming to via email is how to enable or disable all the constraint for single table or database.

Well, in this blog post we will not discuss the reasons why do you need them or what are the advantages or disadvantages of the same. Instead, we will go over scripts to do the same. As lots of users are seeking this script there should be good reason for the same.

-- Disable all table constraints
ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE YourTableName CHECK CONSTRAINT ALL
-- ----------
-- Disable single constraint
ALTER TABLE YourTableName NOCHECK CONSTRAINT YourConstraint
-- Enable single constraint
ALTER TABLE YourTableName CHECK CONSTRAINT YourConstraint
-- ----------
-- Disable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- Enable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Let me know if there is any better way to do the same.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)