SQL Server视图依赖增强：关联填充表的存储过程

我参考Stack Overflow上的一篇帖子调整逻辑后，实现了展示所有视图及其依赖表/视图的层级查询，能正确呈现视图间的依赖层级。现在希望增强功能：当视图依赖某张表时，将填充该表的存储过程也纳入现有层级体系中。目前能手动实现这个功能，但不知道如何整合到已有的层级查询里，求可行方案。

当前查询结果 #

![当前结果]

期望实现效果 #

![增强效果]

现有代码 #

-- 以下逻辑列出所有视图及其依赖的表
;with ObjectHierarchy ( Base_Object_Id , Base_Cchema_Id , Base_Object_Name , Base_Object_Type, object_id , Schema_Id , Name , Type_Desc , Level , Obj_Path) 
as 
    ( select  so.object_id as Base_Object_Id 
        , so.schema_id as Base_Cchema_Id 
        , so.name as Base_Object_Name 
        , so.type_desc as Base_Object_Type
        , so.object_id as object_id 
        , so.schema_id as Schema_Id 
        , so.name 
        , so.type_desc 
        , 0 as Level 
        , convert ( nvarchar ( 1000 ) , N'/' + so.name ) as Obj_Path 
    from sys.objects so 
        left join sys.sql_expression_dependencies ed on ed.referenced_id = so.object_id 
        left join sys.objects rso on rso.object_id = ed.referencing_id 
    where --rso.type is null 
         so.type in ( 'P', 'V', 'IF', 'FN', 'TF' ) 

    union all 

    select   cp.Base_Object_Id as Base_Object_Id 
        , cp.Base_Cchema_Id 
        , cp.Base_Object_Name 
        , cp.Base_Object_Type
        , so.object_id as object_id 
        , so.schema_id as ID_Schema 
        , so.name 
        , so.type_desc 
        , Level + 1 as Level 
        , convert ( nvarchar ( 1000 ) , cp.Obj_Path + N'/' + so.name ) as Obj_Path 
    from sys.objects so 
        inner join sys.sql_expression_dependencies ed on ed.referenced_id = so.object_id 
        inner join sys.objects rso on rso.object_id = ed.referencing_id 
        inner join ObjectHierarchy as cp on rso.object_id = cp.object_id and rso.object_id <> so.object_id 
    where so.type in ( 'P', 'V', 'IF', 'FN', 'TF', 'U') 
        and ( rso.type is null or rso.type in ( 'P', 'V', 'IF', 'FN', 'TF', 'U' ) ) 
        and cp.Obj_Path not like '%/' + so.name + '/%' )   -- 防止层级循环


select  DISTINCT  Base_Object_Name 
    , Base_Object_Type
    , REPLICATE ( '   ' , Level ) + Name as Indented_Name 
    , SCHEMA_NAME ( Schema_Id ) + '.' + Name as object_id 
    , Type_Desc as Object_Type 
    , Level 
    , Obj_Path 
from ObjectHierarchy as p 
WHERE BASE_OBJECT_TYPE = 'VIEW'
order by Obj_Path;


-------  查询填充指定表的存储过程逻辑

SELECT QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id))
FROM   sys.sql_dependencies
WHERE  referenced_major_id = OBJECT_ID((SELECT DISTINCT object_id  FROM ObjectHierarchy))
       AND is_updated = 1
GROUP  BY object_id

要将填充表的存储过程整合到现有层级中，核心思路是在递归CTE的union all分支里新增一个针对**用户表（U）**的关联逻辑，将更新该表的存储过程纳入层级结构，具体实现如下：

修改后的完整SQL代码 #

-- 增强版：视图依赖层级+填充表的存储过程
;with ObjectHierarchy ( Base_Object_Id , Base_Cchema_Id , Base_Object_Name , Base_Object_Type, object_id , Schema_Id , Name , Type_Desc , Level , Obj_Path) 
as 
    ( 
    -- 初始节点：仅从视图开始，简化初始条件
    select  so.object_id as Base_Object_Id 
        , so.schema_id as Base_Cchema_Id 
        , so.name as Base_Object_Name 
        , so.type_desc as Base_Object_Type
        , so.object_id as object_id 
        , so.schema_id as Schema_Id 
        , so.name 
        , so.type_desc 
        , 0 as Level 
        , convert ( nvarchar ( 1000 ) , N'/' + so.name ) as Obj_Path 
    from sys.objects so 
    where so.type = 'V'

    union all 

    -- 分支1：处理视图/对象依赖的表、视图、函数等原有逻辑
    select   cp.Base_Object_Id as Base_Object_Id 
        , cp.Base_Cchema_Id 
        , cp.Base_Object_Name 
        , cp.Base_Object_Type
        , so.object_id as object_id 
        , so.schema_id as Schema_Id 
        , so.name 
        , so.type_desc 
        , cp.Level + 1 as Level 
        , convert ( nvarchar ( 1000 ) , cp.Obj_Path + N'/' + so.name ) as Obj_Path 
    from sys.objects so 
        inner join sys.sql_expression_dependencies ed on ed.referencing_id = cp.object_id and ed.referenced_id = so.object_id
        inner join ObjectHierarchy as cp on cp.object_id <> so.object_id 
    where so.type in ( 'V', 'IF', 'FN', 'TF', 'U') 
        and cp.Obj_Path not like '%/' + so.name + '/%'

    union all

    -- 分支2：关联更新当前表的存储过程
    select  cp.Base_Object_Id as Base_Object_Id
        , cp.Base_Cchema_Id
        , cp.Base_Object_Name
        , cp.Base_Object_Type
        , proc_obj.object_id as object_id
        , proc_obj.schema_id as Schema_Id
        , proc_obj.name as Name
        , proc_obj.type_desc as Type_Desc
        , cp.Level + 1 as Level
        , convert(nvarchar(1000), cp.Obj_Path + N'/[填充表的存储过程]/' + proc_obj.name) as Obj_Path
    from ObjectHierarchy cp
        inner join sys.sql_dependencies dep on dep.referenced_major_id = cp.object_id
        inner join sys.objects proc_obj on proc_obj.object_id = dep.object_id
    where cp.Type_Desc = 'USER_TABLE' -- 仅针对表节点添加存储过程
        and proc_obj.type = 'P' -- 筛选存储过程
        and dep.is_updated = 1 -- 仅保留更新该表的存储过程
        and cp.Obj_Path not like '%/' + proc_obj.name + '/%' -- 避免循环
    )

select  DISTINCT  
    Base_Object_Name 
    , Base_Object_Type
    , REPLICATE ( '   ' , Level ) + Name as Indented_Name 
    , SCHEMA_NAME ( Schema_Id ) + '.' + Name as Full_Object_Name 
    , Type_Desc as Object_Type 
    , Level 
    , Obj_Path 
from ObjectHierarchy as p 
order by Obj_Path;

关键说明 #

• 初始节点简化：直接从视图开始，过滤掉无关的初始对象，减少冗余数据。
• 双递归分支：原有分支维持视图到依赖对象的层级关系，新增分支专门处理表与填充它的存储过程的关联，逻辑清晰。
• 路径标识：在存储过程的层级路径中添加[填充表的存储过程]标记，明确存储过程的角色，便于区分普通依赖对象。
• 循环防护：通过路径匹配确保存储过程不会被重复加入同一层级链路，避免递归循环。

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