SQL Server: Disable/Enable all Triggers in Database
You can use following script to accomplish the given task:
DECLARE @exec bit
DECLARE @db varchar(100)
DECLARE @sql VARCHAR(255)
DECLARE @trg varchar(100)
DECLARE @table varchar(100)
/*init*/
select @db = DB_NAME()
SET @exec = 0
DECLARE gd CURSOR LOCAL FOR
SELECT
sop.name as name_parent ,
so.name
FROM sysobjects AS so
INNER JOIN sysobjects AS sop ON sop.id = so.parent_obj
WHERE
so.xtype = 'TR'
OPEN gd
FETCH NEXT FROM gd INTO @table , @trg
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DISABLE TRIGGER ' + @db + '.' + @trg + ' ON ' + @db + '.' + @table
PRINT @sql
if @exec = 1
EXEC( @sql )
FETCH NEXT FROM gd INTO @table , @trg
END
CLOSE gd
DEALLOCATE gd
DECLARE @exec bit
DECLARE @db varchar(100)
DECLARE @sql VARCHAR(255)
DECLARE @trg varchar(100)
DECLARE @table varchar(100)
/*init*/
select @db = DB_NAME()
SET @exec = 0
DECLARE gd CURSOR LOCAL FOR
SELECT
sop.name as name_parent ,
so.name
FROM sysobjects AS so
INNER JOIN sysobjects AS sop ON sop.id = so.parent_obj
WHERE
so.xtype = 'TR'
OPEN gd
FETCH NEXT FROM gd INTO @table , @trg
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DISABLE TRIGGER ' + @db + '.' + @trg + ' ON ' + @db + '.' + @table
PRINT @sql
if @exec = 1
EXEC( @sql )
FETCH NEXT FROM gd INTO @table , @trg
END
CLOSE gd
DEALLOCATE gd
Comments
Write the first comment
Leave a trace
Post being viewed right now
