List Out All User Defined Triggers

,

Copy the script in SSMS

Run it.

SET NOCOUNT ON
DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR 
SELECT NAME 
      FROM   sys.databases 
      WHERE  database_id IN (SELECT dbid 
                             FROM   sys.sysdatabases d 
                             EXCEPT 
                             SELECT database_id 
                             FROM   msdb..suspect_pages) 
             AND NAME NOT IN ('tempdb','distribution' ) 
             --AND compatibility_level > 80  
            AND state_desc = 'ONLINE' 
            AND source_database_id IS NULL -- Excludes snapshot 
      --ORDER  BY [seq]
	  OPTION (FAST 10)

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
   
   declare @cmd nvarchar(4000);
   select @cmd = N'use ' + quotename(@DB_Name)  + N';'
   --exec sp_executesql @cmd

   select @cmd = @cmd + '
	SELECT db_name() as Database_Name,

	TRIG.name as Trigger_Name,
	TAB.name as Associated_Table_Name,
	trig.is_ms_shipped, --0; user trigger
	TRIG.is_disabled,
    Comments.Text TriggerText
FROM [sys].[triggers] as TRIG
    Inner Join sys.tables as TAB on TRIG.parent_id = TAB.object_id
    Inner Join syscomments Comments On TRIG.object_id = Comments.id
	Inner Join sys.objects as o on o.object_id = tab.object_id
WHERE
    TRIG.is_disabled = 1;' --disabled

--select @cmd
 exec sp_executesql @cmd

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor

SET NOCOUNT OFF

Rate

2.33 (3)

Share

Share

Rate

2.33 (3)