SELECT
    OBJECT_NAME(PARENT_OBJECT_ID) AS PARENT_TABLE,
    OBJECT_NAME(OBJECT_ID) TRIGGER_TABLE,
    *
FROM
SYS.OBJECTS
WHERE TYPE = 'TR'

上面的代码是错误的，如下所示:

SELECT 
    sysobjects.name AS trigger_name 
    --,USER_NAME(sysobjects.uid) AS trigger_owner 
    --,s.name AS table_schema 
    --,OBJECT_NAME(parent_obj) AS table_name 
    --,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    --,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    --,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    --,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    --,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    --,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR' 
EXCEPT
SELECT OBJECT_NAME(parent_id) as Table_Name FROM sys.triggers

SELECT
   ServerName   = @@servername,
   DatabaseName = db_name(),
   SchemaName   = isnull( s.name, '' ),
   TableName    = isnull( o.name, 'DDL Trigger' ),
   TriggerName  = t.name, 
   Defininion   = object_definition( t.object_id )

FROM sys.triggers t
   LEFT JOIN sys.all_objects o
      ON t.parent_id = o.object_id
   LEFT JOIN sys.schemas s
      ON s.schema_id = o.schema_id
ORDER BY 
   SchemaName,
   TableName,
   TriggerName

你还可以像下面这样获得触发器体:

SELECT      o.[name],
            c.[text]
FROM        sys.objects AS o
INNER JOIN  sys.syscomments AS c
ON      o.object_id = c.id
WHERE   o.[type] = 'TR'

Necromancing。 只是发布，因为到目前为止所有的解决方案都不够完整。

SELECT 
     sch.name AS trigger_table_schema 
    ,systbl.name AS trigger_table_name 
    ,systrg.name AS trigger_name 
    ,sysm.definition AS trigger_definition 
    ,systrg.is_instead_of_trigger



    -- https://stackoverflow.com/questions/5340638/difference-between-a-for-and-after-triggers
    -- Difference between a FOR and AFTER triggers?
    -- CREATE TRIGGER trgTable on dbo.Table FOR INSERT,UPDATE,DELETE
    -- Is the same as
    -- CREATE TRIGGER trgTable on dbo.Table AFTER INSERT,UPDATE,DELETE
    -- An INSTEAD OF trigger is different, and fires before and instead of the insert 
    -- and can be used on views, in order to insert the appropriate values into the underlying tables.
    -- AFTER specifies that the DML trigger is fired only when all operations 
    -- specified in the triggering SQL statement have executed successfully. 
    -- All referential cascade actions and constraint checks also must succeed before this trigger fires. 
    -- AFTER is the default when FOR is the only keyword specified.
    ,CASE WHEN systrg.is_instead_of_trigger = 1 THEN 0 ELSE 1 END AS is_after_trigger 

    ,systrg.is_not_for_replication 
    ,systrg.is_disabled
    ,systrg.create_date 
    ,systrg.modify_date

    ,CASE WHEN systrg.parent_class = 1 THEN 'TABLE' WHEN systrg.parent_class = 0 THEN 'DATABASE' END trigger_class 


    ,CASE 
        WHEN systrg.[type] = 'TA' then 'Assembly (CLR) trigger'
        WHEN systrg.[type] = 'TR' then 'SQL trigger' 
        ELSE '' 
    END AS trigger_type 

    -- https://dataedo.com/kb/query/sql-server/list-triggers 
    -- ,(CASE WHEN objectproperty(systrg.object_id, 'ExecIsUpdateTrigger') = 1
    --      THEN 'UPDATE ' ELSE '' END 
    -- + CASE WHEN objectproperty(systrg.object_id, 'ExecIsDeleteTrigger') = 1
    --      THEN 'DELETE ' ELSE '' END
    -- + CASE WHEN objectproperty(systrg.object_id, 'ExecIsInsertTrigger') = 1
    --      THEN 'INSERT' ELSE '' END
    -- ) AS trigger_event 

    ,
    ( 
        STUFF 
        ( 
            ( 
                SELECT 
                    ', ' + type_desc AS [text()]
                    -- STRING_AGG(type_desc, ', ') AS foo 
                FROM sys.events AS syse 
                WHERE syse.object_id = systrg.object_id
                FOR XML PATH(''), TYPE 
                -- GROUP BY syse.object_id 
            ).value('.[1]', 'nvarchar(MAX)') 
            , 1, 2, '' 
        ) 
    ) AS trigger_event_groups 

    -- ,CASE WHEN systrg.parent_class = 1 THEN 'TABLE' WHEN systrg.parent_class = 0 THEN 'DATABASE' END trigger_class  

    ,'DROP TRIGGER "' + sch.name + '"."' + systrg.name + '"; ' AS sql 
    -- ,systrg.*
FROM sys.triggers AS systrg 

LEFT JOIN sys.sql_modules AS sysm 
    ON sysm.object_id = systrg.object_id 

-- sys.objects for view triggers 
-- LEFT JOIN sys.objects AS systbl ON systbl.object_id = systrg.object_id 

-- inner join if you only want table-triggers 
LEFT JOIN sys.tables AS systbl ON systbl.object_id = systrg.parent_id 

LEFT JOIN sys.schemas AS sch 
    ON sch.schema_id = systbl.schema_id 

WHERE (1=1) 

-- AND sch.name IS NOT NULL 
-- AND sch.name IS NULL 
-- AND sch.name = 'dbo' 
-- And here, exclude some triggers with a certain naming schema 
/*  
AND 
(
    -- systbl.name IS NULL 
    -- OR 
    NOT 
    (
        systrg.name = 'TRG_' + systbl.name  + '_INSERT_History'
        OR 
        systrg.name = 'TRG_' + systbl.name  + '_UPDATE_History'
        OR 
        systrg.name = 'TRG_' + systbl.name  + '_DELETE_History'
    )
)
*/

ORDER BY 
     sch.name 
    ,systbl.name 
    ,systrg.name 

SELECT tbl.name as Table_Name,trig.name as Trigger_Name,trig.is_disabled  
FROM [sys].[triggers] as trig inner join sys.tables as tbl on 
trig.parent_id = tbl.object_id