需要列出SQL Server数据库中具有表名和表架构的所有触发器

有一种方法:

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' 

编辑 : 已将用于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'

干得好。

    SELECT
    [so].[name] AS [trigger_name],
    USER_NAME([so].[uid]) AS [trigger_owner],
    USER_NAME([so2].[uid]) AS [table_schema],
    OBJECT_NAME([so].[parent_obj]) AS [table_name],
    OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate],
    OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete],
    OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert],
    OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter],
    OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],
    OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects AS [so]
INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
WHERE [so].[type] = 'TR'

这里有几件事。。。

另外,我看到您试图获取父表的模式信息,我相信为了这样做,您还需要连接sysobjects表本身,以便能够正确获取父表的模式信息。上面的查询就是这样做的。此外,结果中不需要sysusers表,因此连接已被删除。

使用SQL 2000、SQL 2005和SQL 2008 R2进行测试

我最近也有同样的任务,我在sql server 2012数据库中使用了以下命令。使用management studio并连接到要搜索的数据库。然后执行以下脚本。

Select 
[tgr].[name] as [trigger name], 
[tbl].[name] as [table name]

from sysobjects tgr 

join sysobjects tbl
on tgr.parent_obj = tbl.id

WHERE tgr.xtype = 'TR'

您还可以获得触发器主体,如下所示:

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'

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) as Table_Name, * FROM [Database_Name].sys.triggers

它简单实用。

你觉得这个怎么样:非常短而且整洁:)

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 ;

SELECT 
     sysobjects.name AS trigger_name   ,OBJECT_NAME(parent_obj) AS table_name ,s.name AS table_schema 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 


    ,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' 

这对我有用

这就是我所用的(通常是用模型中的东西包装的):

Select
  [Parent] = Left((Case When Tr.Parent_Class = 0 Then '(Database)' Else Object_Name(Tr.Parent_ID) End), 32),
  [Schema] = Left(Coalesce(Object_Schema_Name(Tr.Object_ID), '(None)'), 16),
  [Trigger name] = Left(Tr.Name, 32), 
  [Type] = Left(Tr.Type_Desc, 3), -- SQL or CLR
  [MS?] = (Case When Tr.Is_MS_Shipped = 1 Then 'X' Else ' ' End),
  [On?] = (Case When Tr.Is_Disabled = 0 Then 'X' Else ' ' End),
  [Repl?] = (Case When Tr.Is_Not_For_Replication = 0 Then 'X' Else ' ' End),
  [Event] = Left((Case When Tr.Parent_Class = 0 
                       Then (Select Top 1 Left(Te.Event_Group_Type_Desc, 40)
                             From Sys.Trigger_Events As Te
                             Where Te.Object_ID = Tr.Object_ID)
                       Else ((Case When Tr.Is_Instead_Of_Trigger = 1 Then 'Instead Of ' Else 'After ' End)) +
                             SubString(Cast((Select [text()] = ', ' + Left(Te.Type_Desc, 1) + Lower(SubString(Te.Type_Desc, 2, 32)) +
                                                    (Case When Te.Is_First = 1 Then ' (First)' When Te.Is_Last = 1 Then ' (Last)' Else '' End)
                                             From Sys.Trigger_Events As Te
                                             Where Te.Object_ID = Tr.Object_ID
                                             Order By Te.[Type]
                                             For Xml Path ('')) As Character Varying), 3, 60) End), 60)
  -- If you like: 
  -- , [Get text with] = 'Select Object_Definition(' + Cast(Tr.Object_ID As Character Varying) + ')'
From 
  Sys.Triggers As Tr
Order By
  Tr.Parent_Class, -- database triggers first
  Parent -- alphabetically by parent

正如你所见,这是一个斯柯什更多麦格维尔,但我认为这是值得的:

Parent                           Schema           Trigger name                     Type MS?  On?  Repl? Event
-------------------------------- ---------------- -------------------------------- ---- ---- ---- ----- -----------------------------------------
(Database)                       (None)           ddlDatabaseTriggerLog            SQL            X     DDL_DATABASE_LEVEL_EVENTS
Employee                         HumanResources   dEmployee                        SQL       X          Instead Of Delete
Person                           Person           iuPerson                         SQL       X          After Insert, Update
PurchaseOrderDetail              Purchasing       iPurchaseOrderDetail             SQL       X    X     After Insert
PurchaseOrderDetail              Purchasing       uPurchaseOrderDetail             SQL       X    X     After Update
PurchaseOrderHeader              Purchasing       uPurchaseOrderHeader             SQL       X    X     After Update
SalesOrderDetail                 Sales            iduSalesOrderDetail              SQL       X    X     After Insert, Update, Delete
SalesOrderHeader                 Sales            uSalesOrderHeader                SQL       X          After Update (First)
Vendor                           Purchasing       dVendor                          SQL       X          Instead Of Delete
WorkOrder                        Production       iWorkOrder                       SQL       X    X     After Insert
WorkOrder                        Production       uWorkOrder                       SQL       X    X     After Update

(向右滚动可查看最后一列和最有用的列)

使用此查询:

    SELECT     
        DB_NAME() AS DataBaseName,  
        S.Name AS SchemaName,               
        T.name AS TableName,
        dbo.SysObjects.Name AS TriggerName,
        dbo.sysComments.Text AS SqlContent,
    FROM dbo.SysObjects 
    INNER JOIN dbo.sysComments ON dbo.SysObjects.ID = dbo.sysComments.ID
    INNER JOIN sys.tables AS T ON sysobjects.parent_obj = t.object_id 
    INNER JOIN sys.schemas AS S ON t.schema_id = s.schema_id 
    WHERE dbo.SysObjects.xType = 'TR' 
        AND dbo.SysObjects.Name LIKE 'Permit_AfterInsert' ---- <----- HERE

这可能会有帮助。

SELECT DISTINCT o.[name] AS [Table]
FROM    [sysobjects] o
JOIN    [sysobjects] tr
    ON  o.[id] = tr.[parent_obj]
WHERE   tr.[type] = 'tr'
ORDER BY [Table]

Get a list of tables and all their triggers.

SELECT DISTINCT o.[name] AS [Table], tr.[name] AS [Trigger]
FROM    [sysobjects] o
JOIN    [sysobjects] tr
    ON  o.[id] = tr.[parent_obj]
WHERE   tr.[type] = 'tr'
ORDER BY [Table], [Trigger]

如果你在找 ALL 触发器,记住MS-SQL有两个基于SQL的触发器( sysobjects.type = 'TR' )和基于CLR的触发器( sysobjects.type = 'TA' ).

上面的代码不正确,如图所示:

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 
     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 

    CREATE TABLE [dbo].[VERSIONS](
        [ID] [uniqueidentifier] NOT NULL,
        [DATE] [varchar](100) NULL,
        [SERVER] [varchar](100) NULL,
        [DATABASE] [varchar](100) NULL,
        [USER] [varchar](100) NULL,
        [OBJECT] [varchar](100) NULL,
        [ACTION] [varchar](100) NULL,
        [CODE] [varchar](max) NULL,
     CONSTRAINT [PK_VERSIONS] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[VERSIONS] ADD  CONSTRAINT [DF_VERSIONS_ID]  DEFAULT (newid()) FOR [ID]
    GO


    DROP TRIGGER [DB_VERSIONS_TRIGGER] ON ALL SERVER

    CREATE TRIGGER [DB_VERSIONS_TRIGGER] ON ALL SERVER FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, 
    CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_VIEW, ALTER_VIEW, 
    DROP_VIEW, CREATE_TABLE, ALTER_TABLE, DROP_TABLE 
    AS 
    SET NOCOUNT ON SET XACT_ABORT OFF; 
    BEGIN 
        TRY 
            DECLARE @DATA XML = EVENTDATA() 
            DECLARE @SERVER VARCHAR(100) = @DATA.value('(EVENT_INSTANCE/ServerName)[1]','VARCHAR(100)') 
            DECLARE @DATABASE VARCHAR(100) = @DATA.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(100)') 
            DECLARE @USER VARCHAR(100) = @DATA.value('(/EVENT_INSTANCE/LoginName)[1]','VARCHAR(100)') 
            DECLARE @OBJECT VARCHAR(100) = @DATA.value('(EVENT_INSTANCE/ObjectName)[1]','VARCHAR(100)') 
            DECLARE @ACTION VARCHAR(100) = @DATA.value('(/EVENT_INSTANCE/EventType)[1]','VARCHAR(100)') 
            DECLARE @CODE VARCHAR(MAX) = @DATA.value('(/EVENT_INSTANCE//TSQLCommand)[1]','VARCHAR(MAX)' ) 

            IF OBJECT_ID('DB_VERSIONS.dbo.VERSIONS') IS NOT NULL 
            BEGIN 
                INSERT INTO [DB_VERSIONS].[dbo].[VERSIONS]([SERVER], [DATABASE], [USER], [OBJECT], [ACTION], [DATE], [CODE]) VALUES (@SERVER, @DATABASE, @USER, @OBJECT, @ACTION, getdate(), ISNULL(@CODE, 'NA')) 
            END 
        END 
        TRY 
        BEGIN 
            CATCH 
        END 
    CATCH 
    RETURN

一个困难是文本或描述有换行符。我笨手笨脚的笨手笨脚的,想把它做成一个表格,就是加一个 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;

你可能仍然需要用标签把描述放到一个字段中,但至少在一行,我觉得这很有帮助。

婴儿床: 我最终得到了这个超级通用的一行。希望这对最初的海报和/或刚在Google上输入相同问题的人都有用:

SELECT TriggerRecord.name as TriggerName,ParentRecord.name as ForTableName 
FROM sysobjects TriggerRecord 
INNER JOIN sysobjects ParentRecord ON TriggerRecord.parent_obj=ParentRecord.id 
WHERE TriggerRecord.xtype='TR'

查询特征:

• 可用于任何SQL数据库(即初始目录)
• 不言而喻
• 一份声明
• 可直接粘贴到大多数语言的大多数IDE中

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