How to Temporarily Disable Check Constraints and Database Triggers

Sometimes you will need to change / update / restore some data in a table without the hassle of check constraints getting all up in your face. To do this, you can simply disable and re-enable these via the following commands.

To disable check constraints on a table by executing:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To re-enable check constraints:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

It may also be worth while considering whether you need to do the same to your database triggers while the update is taking place too. This can also be achieved via;

EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
GO

Then to enable the triggers again;

EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"
GO