Little SQL Server Tricks: Disable All Triggers and Constrains of a Table

For our light-weight data synchronisation tool we need to disable all triggers and constrains on our tables until the data is consistent again. In SQL Server we can use these commands to disable everything, do our work and then reactivate the checks and triggers:

1
2
3
4
5
6
7
8
9
-- turn all checks off
DISABLE TRIGGER ALL ON your_table_name;
ALTER TABLE your_table_name NOCHECK CONSTRAINT ALL;

-- do the work

-- turn the checks back on
ALTER TABLE your_table_name CHECK CONSTRAINT ALL;
ENABLE TRIGGER ALL ON your_table_name;

Be aware that you disable the data protection of your database. If you do not know what you are doing you may lose data or produce a tremendous mess. Therefore, use this trick with caution!