How to Drop or Delete all Triggers from a Database in SQL Server

Sometime we have requirement to delete/drop all the triggers from a SQL Server Database. The below code can be used to drop all the triggers on all the tables you have created in a SQL Server database. Before you go ahead and run this script, please make sure you have chosen correct database and server as it will delete all the triggers from chosen SQL Server Database.



USE [Database]
GO
 
DECLARE @TriggerName AS VARCHAR(500)
 -- Drop or Delete All Triggers in a Database in SQL Server 
DECLARE DropTrigger CURSOR FOR
  SELECT 
TRG.name AS TriggerName
  
FROM   sys.triggers TRG
         
INNER JOIN sys.tables TBL
                 
ON TBL.OBJECT_ID = TRG.parent_id 
OPEN DropTrigger
 FETCH Next FROM DropTrigger INTO @TriggerName 
WHILE @@FETCH_STATUS = 0
  
BEGIN
      DECLARE 
@SQL VARCHAR(MAX)=NULL
      
SET @SQL='Drop Trigger ' + @TriggerName
      
PRINT 'Trigger ::' + @TriggerName
            
+ ' Droped Successfully'
      
EXEC (@SQL)
      
PRINT @SQL
      
FETCH Next FROM DropTrigger INTO @TriggerName
  
END
CLOSE 
DropTrigger 
DEALLOCATE DropTrigger



22 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This is an awesome post. Really very informative and creative contents. oracle training in chennai

    ReplyDelete
  3. Salesforce communities have an important role in improving the connection among the customers, channel partners and internal employees of a company. Salesforce interview questions and answers

    ReplyDelete
  4. Nice reading, This is an informative information, thanks for sharing this blog.
    Bpm Tools Training in Bangalore

    ReplyDelete
  5. Really wonderful blog! Thanks for taking your valuable time to share this with us. Keep us updated with more such blogs.
    AWS Course in Chennai
    AWS Online Course
    AWS Course in Coimbatore

    ReplyDelete
  6. Baccarat is money making and it's outstanding accessibility. The best In your case it's found that you'll find rather fascinating choices. And that is considered to be a thing that's rather different And it's very something that's rather happy to strike with The most wonderful, as well, is a very good alternative. Furthermore, it's a truly fascinating alternative. It's the simplest way which could generate profits. Superbly prepar The variety of best earning baccarat is the accessibility of generting by far the most cash. Almost as possible is so suitable for you A substitute which can be assured. To a wide variety of efficiency and availability And find out excellent benefits as well.บาคาร่า
    ufa
    ufabet
    แทงบอล
    แทงบอล
    แทงบอล

    ReplyDelete
  7. Infycle Technologies offers the Best Data training in chennai and is widely known for its excellence in giving the best Data Science Certification course in Chennai. Providing quality software programming training with 100% placement & to build a solid career for every young professional in the software industry is the ultimate aim of Infycle Technologies. Apart from all, the students love the 100% practical training, which is the specialty of Infycle Technologies. To proceed with your
    career with a solid base, reach Infycle Technologies through 7502633633.

    ReplyDelete
  8. Did you want to set your career towards Oracle? Then Infycle is with you to make this into reality. Infycle Technologies gives the combined and best Oracle course in Chennai, which offers various stages of Oracle such as Oracle PL/SQL, Oracle DBA, etc., along with 100% hands-on training guided by professional tutors in the field. Along with that, the mock interviews will be given to the candidates to face the interviews with complete confidence. Apart from all, the candidates will be placed in the top MNC's with an excellent salary package. To get it all, call 7502633633 and make this happen for your happy lifeBest Oracle Course in Chennai | Infycle Technologies

    ReplyDelete
  9. Great blog.thanks for sharing such a useful information
    QTP Training

    ReplyDelete
  10. Whereas we appreciate the fact that there is a myriad of situations that may crop up any minute, we pride ourselves on the fact that our employees are endowed with problem-solving skills. They are people who think on their feet. top security companies in London
    They can assess a situation very fast and act appropriately depending on how things unfold and protect you even in extreme or unexpected situations.

    ReplyDelete
  11. Great info. Thanks.
    However, you need delimiters around the trigger name e.g.
    SET @SQL='Drop Trigger [' + @TriggerName + ']'

    Otherwise may fail.

    ReplyDelete