如何用代码实现SSMS中‘查看依赖项’的表依赖层级查询？

SSMS的“查看依赖项”功能会同时捕获schema-bound和部分非schema-bound的依赖（比如静态SQL中的引用），而sys.sql_expression_dependencies仅记录schema-bound依赖，这是你之前查询无输出的核心原因。以下是用递归CTE实现的扁平化查询，可复现SSMS中“所选对象依赖的对象”的层级结果：

DECLARE @ObjectName NVARCHAR(128) = 'YourTargetObject'; -- 替换为目标对象名
DECLARE @SchemaName NVARCHAR(128) = 'dbo'; -- 替换为目标对象架构名

WITH ObjectDependencies AS (
    -- 初始节点：目标对象自身
    SELECT 
        referenced_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)),
        referencing_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)),
        referencing_schema = @SchemaName,
        referencing_name = @ObjectName,
        referenced_schema = SCHEMA_NAME(OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName))),
        referenced_name = @ObjectName,
        referenced_type_desc = OBJECTPROPERTY(OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)), 'ObjectType'),
        Level = 0
    UNION ALL
    -- 递归查询所有依赖的对象
    SELECT 
        r.referenced_id,
        o.referencing_id,
        referencing_schema = SCHEMA_NAME(o.referencing_id),
        referencing_name = OBJECT_NAME(o.referencing_id),
        referenced_schema = SCHEMA_NAME(r.referenced_id),
        referenced_name = r.referenced_entity_name,
        referenced_type_desc = CASE r.referenced_class
                                    WHEN 1 THEN OBJECTPROPERTY(r.referenced_id, 'ObjectType')
                                    WHEN 6 THEN 'TYPE'
                                    ELSE 'OTHER'
                                END,
        Level = o.Level + 1
    FROM ObjectDependencies o
    CROSS APPLY sys.dm_sql_referenced_entities(
        QUOTENAME(SCHEMA_NAME(o.referencing_id)) + '.' + QUOTENAME(o.referencing_name), 
        'OBJECT'
    ) r
    WHERE r.referenced_id IS NOT NULL -- 排除无法解析的依赖（如动态SQL中的模糊引用）
      AND NOT EXISTS (
          SELECT 1 FROM ObjectDependencies 
          WHERE referenced_id = r.referenced_id AND referencing_id = o.referencing_id
      ) -- 避免循环依赖重复记录
)
-- 扁平化输出：排除自身，仅展示依赖的对象
SELECT DISTINCT
    Level,
    referencing_schema,
    referencing_name,
    referenced_schema,
    referenced_name,
    referenced_type_desc
FROM ObjectDependencies
WHERE Level > 0
ORDER BY Level, referenced_schema, referenced_name;

关键说明： #

• 使用sys.dm_sql_referenced_entities替代sys.sql_expression_dependencies，该函数能解析更多非schema-bound依赖（如静态SQL中的跨对象引用）。
• Level字段表示依赖层级：1为直接依赖，2为间接依赖，以此类推。
• 若查询仍无输出，说明目标对象确实没有可被解析的依赖，或依赖存在于无法被静态解析的动态SQL中（这种情况SSMS UI也无法捕获）。

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