List for all disable triggers-CodePudding

I need to make a list of all triggers which aren't running.

declare @Trigger nvarchar(150) 
select @Trigger = t1.name from  sys.triggers as t1 WHERE t1.is_disabled = 1
declare @disable int 
select @disable =t1.is_disabled FROM sys.triggers t1 WHERE t1.is_disabled = 0
While @disable <= 1
BEGIN
    Print 'Trigger '   @Trigger    ' is disable!'
    set @disable = @disable   1
END

As a result I have only one trigger, but there are another 10 which are disabled.

Any ideas?

CodePudding user response：

This should do the trick

SELECT 
    t2.[name] TableTriggerReference
    , SCHEMA_NAME(t2.[schema_id]) TableSchemaName
    , t3.[rowcnt] TableReferenceRowCount
    , t1.[name] TriggerName
    , 'ALTER TABLE '   QUOTENAME(SCHEMA_NAME(t2.schema_id))   '.'   QUOTENAME(t2.[name])   ' ENABLE TRIGGER '   QUOTENAME(t1.[name]) ScriptToEnableThem
FROM sys.triggers t1
    INNER JOIN sys.tables t2 ON t2.object_id = t1.parent_id
    INNER JOIN sys.sysindexes t3 On t2.object_id = t3.id
WHERE t1.is_disabled = 1
    AND t1.is_ms_shipped = 0
    AND t1.parent_class = 1

CodePudding user response：

select *
from sys.triggers trg  
where is_disabled = 1