需要用表名和表的模式列出SQL Server数据库中的所有触发器

我需要列出SQL Server数据库中的所有触发器表名和表的模式。

我马上就要讲到这个了:

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'

我还需要得到表的模式。

当前回答

c# Cribs:我最终得到了这个超级通用的一行程序。希望这对原始海报和/或刚刚输入我在谷歌中做的相同问题的人都有用:

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中

2019-03-12 01:16:22

其他回答

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

2015-11-06 08:50:15

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

2019-04-04 08:16:33

我最近有同样的任务，我使用以下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'

2013-03-28 19:07:38

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'

这对我很有用

2014-09-30 12:33:48

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

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

2015-09-08 15:38:13

需要用表名和表的模式列出SQL Server数据库中的所有触发器

推荐文章

最新文章

标签