Query below lists tables with their triggers in a database with details.

Query

SELECT  SubjectTableDatabaseName as TableDatabase,
        TableName as TableName,
        TriggerName,
        CASE ActionTime
            when 'A' then 'AFTER'
            when 'B' then 'BEFORE'
            when 'I' then 'INSTEAD OF'
            end,
        CASE Event
            WHEN 'U' THEN 'UPDATE'
            WHEN 'I' THEN 'INSERT'
            WHEN 'D' THEN 'DELETE'
            END,
        CASE EnabledFlag
            WHEN 'Y' THEN 'ENABLED'
            WHEN 'N' THEN 'DISABLED'
            END as Status,
        CASE Kind
            WHEN 'R' THEN 'ROW'
            WHEN 'S' THEN 'STATEMENT'
            end as TriggerKind,
        RequestText as Definition,
        CreateTimeStamp as CreateDate
FROM    DBC.TriggersV
ORDER BY    TableDatabase,
            TableName;

Columns

• TableDatabase - name of the trigger table database
• TableName - name of the trigger table
• TriggerName - name of the trigger
• ActionTime - trigger activation time: before, after
• Event - specific SQL operation: insert, update or delete
• Status - trigger status
  • ENABLED
  • DISABLED
• TriggerKind - trigger fires for each:
  • ROW - row modified in the subject table
  • STATEMENT - statement that modifies the subject table
• Definition - SQL definiton of trigger
• CreateDate - trigger creation time

Rows

• One row represents one trigger
• Scope of rows: all triggers in a database
• Ordered by table database name and table name

Sample results