0

Disable/Enable all triggers in SQL Server Database

Posted by Chris Straw on May 12, 2012 in SQL Server |

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”

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright © 2011-2019 Chris Straw All rights reserved.
This site is using the Desk Mess Mirrored theme, v2.5, from BuyNowShop.com.