SQL Server: Disable All Constraints

Posted on May 3, 2011 by admin

To disable all constraints on all tables in a database:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

1	EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To enable all constraints on all tables in a database:

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

1	exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

YAGNI