T-SQL: Disable Constraint Checks On All Tables With A Single Line Query

Microsoft’s “undocumented” but quite useful stored procedure, sp_msforeachtable, you can easily disable constraint checks on all tables in a database. Here’s how:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To re-enable the checks after you are done, simply change NOCHECK to CHECK, e.g.

EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"

Hope this helps!

This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *