如何对Information_Schema.Tables与sys.triggers执行Inner join关联查询

关联字段说明 #

sys.triggers系统视图中的parent_id字段是关联的核心字段，对于表级触发器而言，parent_id的值就是触发器所属基表的object_id唯一标识。

方法1：直接关联Information_Schema.Tables与sys.triggers #

通过OBJECT_ID函数将Information_Schema.Tables中的 schema 名和表名转换为对应的object_id，和sys.triggers.parent_id进行关联：

SELECT t.*, tr.*
FROM Information_Schema.Tables t
INNER JOIN sys.triggers tr 
    ON tr.parent_id = OBJECT_ID(QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME))
WHERE 
    t.TABLE_TYPE = 'BASE TABLE'
    AND tr.name LIKE '%Log%'
    -- 可选筛选：仅保留表级触发器，排除其他类型对象的触发器
    AND tr.parent_class = 1

方法2：通过sys系列系统表关联 #

更推荐的写法，直接用原生系统表关联，性能更稳定：

SELECT 
    s.name AS TABLE_SCHEMA,
    t.name AS TABLE_NAME,
    t.create_date AS TABLE_CREATE_TIME,
    tr.name AS TRIGGER_NAME,
    tr.create_date AS TRIGGER_CREATE_TIME,
    tr.is_disabled AS TRIGGER_STATUS,
    sm.definition AS TRIGGER_DEFINITION -- 可选：获取触发器的完整SQL定义
FROM sys.tables t
INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id
INNER JOIN sys.triggers tr 
    ON t.object_id = tr.parent_id
-- 可选关联：获取触发器定义代码
LEFT JOIN sys.sql_modules sm
    ON tr.object_id = sm.object_id
WHERE 
    t.type = 'U' -- 等价于BASE TABLE，代表用户创建的基表
    AND tr.name LIKE '%Log%'

其他关联查询方式 #

• 单表触发器查询：如果需要查询指定表的所有触发器，可以使用sys.dm_sql_referencing_entities动态管理视图，传入表的完整名称即可查询所有引用该表的触发器对象
• 旧版本兼容方案：SQL Server旧版本中可以使用sp_depends '表名'存储过程查询表关联的触发器，但该存储过程已被官方弃用，不建议在生产环境使用

内容的提问来源于stack exchange，提问作者Esteban Giraldo