How to Disable All the Triggers in SQL Server Database

Sometime we have requirement that we need to disable all the triggers in SQL Server Database. We can use Cursor in TSQL To loop through list of Triggers and then disable them.

The below script can be use to disable all the triggers in SQL Server Database.

USE [Database]

GO
 DECLARE @TriggerName AS VARCHAR(500) 
DECLARE @TableName AS VARCHAR(500) 
DECLARE @SchemaName AS VARCHAR(100) 
--Disable All Triggers in a Database in SQL Server
 DECLARE DisableTrigger CURSOR FOR
SELECT TBL.name                   AS TableName,
       Schema_name(TBL.schema_id) AS Table_SchemaName,
       TRG.name                   AS TriggerName 
FROM   sys.triggers TRG
       INNER JOIN sys.tables TBL
               ON TBL.OBJECT_ID = TRG.parent_id 
               AND TRG.is_disabled=0 
               AND TBL.is_ms_shipped=0
 OPEN DisableTrigger 
FETCH Next FROM DisableTrigger INTO @TableName,@SchemaName,@TriggerName 
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL VARCHAR(MAX)=NULL

      SET @SQL='Disable Trigger ' + @TriggerName +' ON '+@SchemaName+'.'+@TableName

      EXEC (@SQL)
      PRINT 'Trigger ::' + @TriggerName + 'is disabled on '+@SchemaName+'.'+@TableName
      PRINT @SQL

      FETCH Next FROM DisableTrigger INTO @TableName,@SchemaName,@TriggerName
  END

CLOSE DisableTrigger
DEALLOCATE DisableTrigger



To check if all the triggers are disabled correctly in SQL Server Database, use below query

SELECT TBL.name                   AS TableName,
       Schema_name(TBL.schema_id) AS Table_SchemaName,
       TRG.name                   AS TriggerName,
       TRG.parent_class_desc,
       CASE
         WHEN TRG.is_disabled = 0 THEN 'Enable'
         ELSE 'Disable'
       END                        AS TRG_Status 
FROM   sys.triggers TRG
       INNER JOIN sys.tables TBL
               ON TBL.OBJECT_ID = TRG.parent_id 
              AND trg.is_disabled=1 --use this filter to get Disabled Triggers