如何在SQL Server中递归查询存储过程与表的数据血缘依赖链

适用于存储过程生成数据表、表与存储过程嵌套依赖的数仓环境，解决系统自带DMV仅能返回第一层依赖的问题，支持指定任意存储过程或表作为起点，向上追溯所有上游源表，输出完整依赖链路。

核心实现逻辑 #

• 递归终止规则：当分支追溯到不存在任何上游依赖的源表时，自动终止该分支遍历
• 双向依赖拉取：
  • 遍历到存储过程节点时，调用sys.dm_sql_referenced_entities拉取其引用的所有用户表、存储过程
  • 遍历到用户表节点时，通过sys.sql_modules匹配存储过程定义文本，定位所有生成/写入该表的上游存储过程，继续向上追溯
• 异常防护：递归过程中记录已遍历的对象路径，遇到重复对象直接跳过，避免循环依赖导致的死递归；同时设置最大递归深度阈值，防止异常场景下查询无限制运行

可直接复用的实现代码 #

-- 配置追溯起点
DECLARE @StartObject SYSNAME = N'schema.目标对象名'; -- 例：dbo.usp_GenFactSales 或 dbo.FactSales
DECLARE @StartObjectType CHAR(2) = 'P'; -- 起点为存储过程填'P'，为用户表填'U'

WITH DependencyChain AS (
    -- 锚点：初始化起点对象
    SELECT
        0 AS DepLevel,
        @StartObject AS ObjectName,
        @StartObjectType AS ObjectType,
        CAST(@StartObject AS NVARCHAR(MAX)) AS DepPath,
        CAST(',' + @StartObject + ',' AS NVARCHAR(MAX)) AS VisitedMark
    UNION ALL
    -- 递归部分：逐层向上遍历
    SELECT
        dc.DepLevel + 1 AS DepLevel,
        ref.ObjectName,
        ref.ObjectType,
        CAST(dc.DepPath + ' -> ' + ref.ObjectName AS NVARCHAR(MAX)) AS DepPath,
        CAST(dc.VisitedMark + ref.ObjectName + ',' AS NVARCHAR(MAX)) AS VisitedMark
    FROM DependencyChain dc
    CROSS APPLY (
        -- 当前节点是存储过程：拉取其引用的表、存储过程
        SELECT
            QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS ObjectName,
            o.type AS ObjectType
        FROM sys.dm_sql_referenced_entities(dc.ObjectName, 'OBJECT') r
        JOIN sys.objects o ON r.referenced_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE dc.ObjectType = 'P'
          AND o.type IN ('U','P')
          AND r.referenced_schema_name IS NOT NULL
        UNION ALL
        -- 当前节点是表：拉取写入/生成该表的存储过程
        SELECT
            QUOTENAME(s.name) + '.' + QUOTENAME(p.name) AS ObjectName,
            'P' AS ObjectType
        FROM sys.procedures p
        JOIN sys.schemas s ON p.schema_id = s.schema_id
        JOIN sys.sql_modules m ON p.object_id = m.object_id
        WHERE dc.ObjectType = 'U'
          AND (
              m.definition LIKE '%CREATE TABLE ' + REPLACE(dc.ObjectName, QUOTENAME(s.name)+'.', '') + ' %'
              OR m.definition LIKE '%SELECT INTO ' + REPLACE(dc.ObjectName, QUOTENAME(s.name)+'.', '') + ' %'
              OR m.definition LIKE '%INSERT INTO ' + REPLACE(dc.ObjectName, QUOTENAME(s.name)+'.', '') + ' %'
              OR m.definition LIKE '%MERGE ' + REPLACE(dc.ObjectName, QUOTENAME(s.name)+'.', '') + ' %'
          )
    ) ref
    -- 循环依赖检测：已遍历过的对象不再重复处理
    WHERE dc.VisitedMark NOT LIKE '%,' + ref.ObjectName + ',%'
      AND dc.DepLevel < 20 -- 最大递归深度，可根据实际依赖层级调整
)
-- 结果筛选：仅输出最上游源表及完整依赖路径
SELECT DISTINCT
    DepLevel,
    ObjectName AS UpstreamSourceTable,
    DepPath AS FullDependencyLink
FROM DependencyChain
WHERE ObjectType = 'U'
-- 过滤存在上游生成逻辑的中间表
AND ObjectName NOT IN (
    SELECT DISTINCT QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.sql_modules m 
        ON m.definition LIKE '%CREATE TABLE ' + QUOTENAME(t.name) + ' %'
        OR m.definition LIKE '%SELECT INTO ' + QUOTENAME(t.name) + ' %'
)
ORDER BY DepLevel, UpstreamSourceTable
OPTION (MAXRECURSION 20); -- 与上述最大递归深度保持一致

使用注意事项 #

• 脚本默认不捕获临时表、表变量依赖，如果数仓存储过程大量使用临时表承载中间计算，需要补充对应临时表的关联匹配逻辑
• 存储过程与表的写入匹配关键词需要和自身数仓开发规范对齐，比如存在TRUNCATE TABLE后写入、分区切换写入等场景，要对应调整sys.sql_modules的匹配规则，避免漏判
• 存在跨库依赖时，需要在对应业务库下执行脚本，或给对象名补充库名前缀扩展匹配范围
• 最大递归深度不建议设置超过100，若触发深度阈值报错，优先检查是否存在未被识别的循环依赖，不要盲目拉高阈值

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