如何通过SQL查询获取直接或间接依赖含'sapphire'表的所有视图

逻辑说明 #

要处理直接+间接的多层级依赖查询，标准实现方案是使用递归公共表表达式（CTE），执行逻辑分为两步：

1. 先查询所有直接依赖名称包含sapphire的表的视图作为基础数据集
2. 基于基础数据集递归向上查找所有依赖上述视图的上层视图，直到没有新的匹配结果为止

字段对应规则 #

基于你提供的refs表结构，我们做如下符合业务逻辑的约定：

• depname：被依赖的对象名称（表/视图）
• refname：主动发起依赖的对象名称（表/视图）
• reftype：refname对应的对象类型，值为view代表视图，table代表表

适用场景 #

支持所有兼容递归CTE语法的数据库，包括MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11g R2及以上版本。

实现代码 #

WITH RECURSIVE dependent_views AS (
    -- 锚点：匹配第一层直接依赖含sapphire表的视图
    SELECT 
        DISTINCT refname AS view_name,
        1 AS dependency_level
    FROM refs
    WHERE
        reftype = 'view'
        AND depname LIKE '%sapphire%'
        -- 确保被依赖的是表，不是其他视图
        AND depname IN (SELECT DISTINCT refname FROM refs WHERE reftype = 'table')

    UNION ALL

    -- 递归：匹配间接依赖的上层视图
    SELECT
        DISTINCT r.refname AS view_name,
        dv.dependency_level + 1 AS dependency_level
    FROM refs r
    INNER JOIN dependent_views dv 
        ON r.depname = dv.view_name
    WHERE
        r.reftype = 'view'
        -- 避免循环依赖导致死循环
        AND r.refname NOT IN (SELECT view_name FROM dependent_views)
)
-- 输出最终结果，按依赖层级排序
SELECT DISTINCT view_name, dependency_level
FROM dependent_views
ORDER BY dependency_level ASC, view_name ASC;

注意事项 #

• 如果你的reftype字段中视图、表的枚举值和上述代码不同，请替换为实际存储的对应值
• 若依赖层级超过数据库默认递归深度（如MySQL默认递归深度为1000），可临时调整数据库递归深度参数避免报错
• 若使用不支持递归CTE的旧版本数据库，可通过多次自关联实现，仅适用于依赖层级固定可枚举的场景
• 代码内置了循环依赖校验逻辑，可避免A依赖B、B依赖A的场景导致递归异常

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