Enable and Disable All the Triggers in a SQL Server Database

A database developer recently asked on the MSDN forums for a quick way to enable and disable all the triggers in a SQL Server 2005/2008 database. In my opinion, the quickest option is to use the undocumented stored procedure sp_MSforeachtable

To Disable All the Triggers

sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"

To Enable All the Triggers

sp_MSforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL"

Similarly to Enable and Disable all the Constraints, use this query:

sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: