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



7 comments:

  1. Replies
    1. IEEE Final Year projects Project Center in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes. Final Year Project Domains for IT

      JavaScript Training in Chennai

      JavaScript Training in Chennai

      The Angular Training covers a wide range of topics including Components, project projects for cse. Angular Training

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    sql dba training
    sql server dba online training

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

    ReplyDelete