Recently I’ve had the scenario where I’ve had to drop all SQL Triggers from a Microsoft SQL database.

This could be done manually through the SQL Management Studio, but with a database with lots of triggers can be time consuming to do this.

The following SQL when ran against the database will drop all triggers from the said database automatically:

 USE [{database Name}]
DECLARE @SQLCmd nvarchar(1000)
DECLARE @Trig sysname
DECLARE @owner sysname
DECLARE @uid int

DECLARE TGCursor CURSOR FOR
SELECT name, uid FROM sysobjects WHERE type = 'TR'
OPEN TGCursor
FETCH NEXT FROM TGCursor INTO @Trig, @uid
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLCmd = N'DROP TRIGGER [' + user_name(@uid) + '].[' + @Trig + ']'
EXEC sp_executesql @SQLCmd
PRINT @SQLCmd

FETCH next FROM TGCursor INTO @Trig, @uid
END

CLOSE TGCursor
DEALLOCATE TGCursor