Monday, March 23, 2015

Script to disable/enable all triggers (Database Level) in a database & Enable or Disable Server level triggers (SERVER LEVEL)

Use below script to disable or enable all triggers in MyDatabase  (Database level) 

To disable all triggers, you can use following statement
use MyDatabase
sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

To enable all triggers, you can use following statement
use MyDatabase
sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'


The following example disables all DDL triggers that were created at the server scope. (Server Level Triggers) 
USE master
GO
DISABLE Trigger ALL ON ALL SERVER;
GO

USE master
GO
ENABLE Trigger ALL ON ALL SERVER;
GO