Chris Straw
SHARE:

Disable/Enable all triggers in SQL Server Database

Spread the love

This script will create a script to disable and enable all the trigger within a SQL Server Database.

Select 'DISABLE TRIGGER ' +  sys.triggers.name + ' ' + ' ON ' + ' [' + SCHEMA_NAME(sys.objects.schema_id) + '].[' + sys.objects.NAME + ']
'  +
'go'
from  sys.triggers
join sys.objects on sys.objects.object_id = sys.triggers.parent_id
where sys.triggers.is_disabled=0
ORDER BY sys.triggers.NAME

go

Select 'ENABLE TRIGGER ' +  sys.triggers.name + ' ' + ' ON ' + ' [' + SCHEMA_NAME(sys.objects.schema_id) + '].[' + sys.objects.name + ']
'  +
'go'
from  sys.triggers
join sys.objects on sys.objects.object_id = sys.triggers.parent_id
where sys.triggers.is_disabled=1
ORDER BY sys.triggers.NAME

You will want to change your results to TEXT instead of GRID to get the line breaks for the “go”