.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Procedure to delete all the triggers of the database

Posted By:Virendra Dugar       Posted Date: October 30, 2009    Points: 10    Category: SQL Query    URL: http://www.dotnetspark.com  
 

Procedure to delete all the triggers of the database.

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

Thanks,
Virendra Dugar


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend