如何使用T-SQL查询视图依赖的所有数据库对象

之前查询结果不准的原因 #

单独使用sys.dm_sql_referenced_entities时出现漏查函数、返回冗余表的问题，核心原因有两点：

• 调用时未指定正确的依赖范围参数，默认逻辑只会返回表/视图类的表级依赖，会漏掉SELECT列表、WHERE条件、JOIN条件中调用的标量/表值函数
• 未做结果过滤：返回的冗余内容大多是系统自动生成的内部关联表、同义词解析后的歧义对象、嵌套依赖中未被最终逻辑引用的中间对象

可直接复用的查询代码 #

以下代码逻辑和SSMS「查看依赖项」的递归查询逻辑完全对齐，会自动过滤冗余内容、覆盖所有依赖对象类型（表、视图、函数等）：

-- 替换为你要查询的目标视图，格式为「架构名.视图名」
DECLARE @TargetView NVARCHAR(256) = N'dbo.替换为你的视图名';

WITH DependencyCTE AS (
    -- 锚点：查询视图的第一层直接依赖
    SELECT
        referenced_id AS ObjectID,
        referenced_schema_name AS SchemaName,
        referenced_entity_name AS ObjectName,
        referenced_class_desc AS DepClass,
        1 AS DepLevel
    FROM sys.dm_sql_referenced_entities(@TargetView, N'OBJECT')
    WHERE
        is_ambiguous = 0
        AND referenced_id IS NOT NULL
        AND is_caller_dependent = 0
    UNION ALL
    -- 递归查询所有嵌套依赖
    SELECT
        d.referenced_id AS ObjectID,
        d.referenced_schema_name AS SchemaName,
        d.referenced_entity_name AS ObjectName,
        d.referenced_class_desc AS DepClass,
        c.DepLevel + 1 AS DepLevel
    FROM DependencyCTE c
    CROSS APPLY sys.dm_sql_referenced_entities(
        QUOTENAME(c.SchemaName) + N'.' + QUOTENAME(c.ObjectName),
        N'OBJECT'
    ) d
    WHERE
        d.is_ambiguous = 0
        AND d.referenced_id IS NOT NULL
        AND d.is_caller_dependent = 0
        -- 防止循环引用导致死循环
        AND d.referenced_id NOT IN (SELECT ObjectID FROM DependencyCTE)
)
-- 结果去重、类型映射、过滤冗余系统对象
SELECT DISTINCT
    SchemaName,
    ObjectName,
    CASE DepClass
        WHEN 'OBJECT_OR_COLUMN' THEN
            CASE o.type
                WHEN 'U' THEN '用户表'
                WHEN 'V' THEN '视图'
                WHEN 'FN' THEN '标量函数'
                WHEN 'TF' THEN '表值函数'
                WHEN 'IF' THEN '内联表值函数'
                WHEN 'P' THEN '存储过程'
                ELSE o.type_desc
            END
        ELSE DepClass
    END AS 依赖对象类型,
    MIN(DepLevel) AS 依赖层级
FROM DependencyCTE c
LEFT JOIN sys.objects o ON c.ObjectID = o.object_id
WHERE o.is_ms_shipped = 0 -- 过滤系统生成的冗余内部表
GROUP BY SchemaName, ObjectName, DepClass, o.type, o.type_desc
ORDER BY 依赖层级, SchemaName, ObjectName;

使用说明 #

• 如果只需要查询视图的直接依赖，删除CTE中UNION ALL后面的递归部分即可，输出和SSMS「仅直接依赖」选项结果完全一致
• 执行账号需要拥有目标视图及所有依赖对象的VIEW DEFINITION权限，否则会出现对象遗漏
• 之前查询中缺失的各类函数、返回的冗余表问题，都已经通过参数指定、结果过滤逻辑解决
• 依赖层级为1代表是视图直接引用的对象，数值越大代表是嵌套引用的深层对象

参考输出效果：

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