MS SQL Server: How to drop all triggers in a SQL Server database
This script will loop through and drop all schema triggers in a SQL Server database.
Posted by retroman80s
on Aug 25, 2009
479 Views
Report Abuse
Tags: schema, trigger, sql server 2008, sql server 2005
Here's a handy script to remove all database triggers within different schemas:
DECLARE @TEMP TABLE (TRIGNAME VARCHAR(200));
DECLARE @VAL VARCHAR(200), @STR NVARCHAR(250);
INSERT INTO @TEMP SELECT '['+SCHEMA_NAME(uid)+'].['+name+']' FROM SYSOBJECTS WHERE xtype = 'tr'
DECLARE CR_TRIG CURSOR FOR SELECT TRIGNAME FROM @TEMP
OPEN CR_TRIG
FETCH NEXT FROM CR_TRIG INTO @VAL
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @STR = 'DROP TRIGGER ' + @VAL;
--print @STR
EXECUTE SP_EXECUTESQL @STR
FETCH NEXT FROM CR_TRIG INTO @VAL
END
CLOSE CR_TRIG
DEALLOCATE CR_TRIG