Sql server: Generate sql to disable triggers

This seems to work:

SELECT
     'disable trigger ' + USER_NAME(trig.schema_id) + '.' +  trig.name +
     ' on ' +
     USER_NAME(tab.schema_id) + '.' + OBJECT_NAME(trig.parent_object_id) +
     char(10) +
     'go'
FROM sys.objects trig
INNER JOIN sys.objects tab
    ON trig.parent_object_id = tab.object_id
WHERE trig.type = 'TR'

It generates sql like this:

disable trigger dbo.tr_AfterUpdate on dbo.BigTable
go
disable trigger dbo.tr_BeforeUpdate on dbo.BigTable
go

Notes and Queries - Cathedral, bell tower, stonehenge

Advertisements