How To Disable All Triggers on a Database


To enable & disable all the triggers and constraints we have a system defined Stored Procedure which are as under:

1) Disable all the triggers for a single database:

USE AdventureWorks
;
GO
DISABLE
TRIGGER Person.uAddress ON AdventureWorks
;
GO

2) Disable all the triggers for all servers:

USE AdventureWorks
;
GO
DISABLE
TRIGGER ALL ON ALL SERVER
;
GO

3) Disabling all Constraints
exec sp_MSforeachtable ALTER TABLE ? NOCHECK CONSTRAINT ALL
 
4) Disabling all Triggers
exec sp_MSforeachtable ALTER TABLE ? DISABLE TRIGGER ALL

5) Enabling all Constraints
exec sp_MSforeachtable ALTER TABLE ? CHECK CONSTRAINT ALL

6) Enabling all Triggers
exec sp_MSforeachtable ALTER TABLE ? ENABLE TRIGGER ALL

For more detail plz read:
http://msdn.microsoft.com/en-us/library/ms189748.aspx
Ebook Download
View all
Learn
View all
F11Research & Development, LLC