Procedure to delete all the trigger of the database.

Virendradugar
Posted by Virendradugar under Sql Server category on | Views : 2012
Just create this procedure and execute it once. It will delete all the triggers of the database.

CREATE PROC PR_DROPTRIGGER
AS BEGIN
DECLARE @TEMP TABLE (TRIGNAME VARCHAR(200));
DECLARE @VAL VARCHAR(200), @STR NVARCHAR(250);

INSERT INTO @TEMP SELECT 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
END

Comments or Responses

Login to post response