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 OBJECT_NAME(parent_id) Table_or_ViewNM,
      name TriggerNM,
      is_instead_of_trigger,
      is_disabled
FROM sys.triggers
WHERE parent_class_desc = 'OBJECT_OR_COLUMN'
ORDER BY OBJECT_NAME(parent_id),
Name ;

一个困难是文本或描述有换行。我的笨拙的拼凑，使它在更表格的东西，是添加一个HTML文字到SELECT子句，复制并粘贴到记事本，保存与HTML扩展，在浏览器中打开，然后复制并粘贴到一个电子表格。 例子

SELECT obj.NAME AS TBL,trg.name,sm.definition,'<br>'
FROM SYS.OBJECTS obj
LEFT JOIN (SELECT trg1.object_id,trg1.parent_object_id,trg1.name FROM sys.objects trg1 WHERE trg1.type='tr' AND trg1.name like 'update%') trg
 ON obj.object_id=trg.parent_object_id
LEFT JOIN (SELECT sm1.object_id,sm1.definition FROM sys.sql_modules sm1 where sm1.definition like '%suser_sname()%') sm ON trg.object_id=sm.object_id
WHERE obj.type='u'
ORDER BY obj.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 

这里有一种方法:

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' 

编辑: 注释掉连接到sysusers查询工作在AdventureWorks2008。

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' 

编辑2:用于SQL 2000

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

INNER JOIN sysusers AS s 
    ON o2.uid = s.uid 

WHERE o.type = 'TR'

不需要与其他表连接…所有信息都可以从sys.objects中获取。

SELECT  name as trigger_name
, object_name(parent_obj) as tableName
, object_schema_name(parent_obj) as schemaName 
,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 s
WHERE s.type = 'TR'