需要使用表名和表的架构列出 SQL Server 数据库中的所有触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4305691/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Need to list all triggers in SQL Server database with table name and table's schema
提问by Ronnie Overby
I need to list all triggers in SQL Server database with table name and table's schema.
我需要使用表名和表的架构列出 SQL Server 数据库中的所有触发器。
I'm almost there with this:
我几乎就在那里：
SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj),
isupdate = OBJECTPROPERTY( id, 'ExecIsUpdateTrigger'), isdelete = OBJECTPROPERTY( id, 'ExecIsDeleteTrigger'),
isinsert = OBJECTPROPERTY( id, 'ExecIsInsertTrigger'), isafter = OBJECTPROPERTY( id, 'ExecIsAfterTrigger'),
isinsteadof = OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger'),
[disabled] = OBJECTPROPERTY(id, 'ExecIsTriggerDisabled')
FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
WHERE type = 'TR'
I just need to get the table's schema also.
我也只需要获取表的架构。
回答by Joe Stefanelli
Here's one way:
这是一种方法：
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'
EDIT: Commented out join to sysusers for query to work on AdventureWorks2008.
编辑：注释掉加入 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'
EDIT 2: For SQL 2000
编辑 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'
回答by Salizar Marxx
Here you go.
干得好。
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'
A couple of things here...
这里有几件事......
Also I see that you were attempting to pull the parent tables schema information, I believe in order to do so you would also need to join the sysobjects table on itself so that you can correctly get the schema information for the parent table. the query above does this. Also the sysusers table wasn't needed in the results so that Join has been removed.
此外，我看到您正在尝试提取父表架构信息，我相信为此您还需要加入 sysobjects 表本身，以便您可以正确获取父表的架构信息。上面的查询就是这样做的。此外，结果中不需要 sysusers 表，因此 Join 已被删除。
tested with SQL 2000, SQL 2005, and SQL 2008 R2
使用 SQL 2000、SQL 2005 和 SQL 2008 R2 进行测试
回答by bsivel
I had the same task recently and I used the following for sql server 2012 db. Use management studio and connect to the database you want to search. Then execute the following script.
我最近有同样的任务，我对 sql server 2012 db 使用了以下内容。使用 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'
回答by Serjik
You can also get the body of triggers as following:
您还可以按如下方式获取触发器主体：
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'
回答by mmuttam
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
回答by BijaN-R
Use this query :
使用此查询：
SELECT OBJECT_NAME(parent_id) as Table_Name, * FROM [Database_Name].sys.triggers
It's simple and useful.
它简单而有用。
回答by InfinityGoesAround
And what do you think about this: Very short and neat :)
你怎么看这个：非常简短和整洁:)
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 ;
回答by Stu
This is what I use (usually wrapped in something I stuff in Model):
这是我使用的（通常包含在我在模型中填充的东西中）：
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
As you see it is a skosh more McGyver, but I think it's worth it:
正如你所看到的，它更像是 McGyver，但我认为这是值得的：
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
(Scroll right to see the final and most useful column)
（向右滚动以查看最后也是最有用的一栏）
回答by Sapan Mohanty
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'
this working for me
这对我有用
回答by Ardalan Shahgholi
Use This Query :
使用此查询：
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
