如何用Snowflake的get_object_references获取视图cplx_vw1的全层级表依赖？

原SQL存在两个核心问题：

• 当第一层引用返回多个对象时，单值子查询会触发多行结果错误，无法正常调用GET_OBJECT_REFERENCES
• 仅处理了第一层依赖，没有递归遍历嵌套视图的深层引用

以下是可以逐层拆解所有依赖、最终定位到底层表的递归CTE方案：

WITH RECURSIVE view_dependencies AS (
    -- 初始层：获取目标视图的直接引用
    SELECT
        1 AS dependency_level,
        referenced_database_name,
        referenced_schema_name,
        referenced_object_name,
        referenced_object_type,
        CONCAT(referenced_database_name, '.', referenced_schema_name, '.', referenced_object_name) AS full_object_path,
        CONCAT('prod1.PUBLIC.cplx_vw1 -> ', full_object_path) AS dependency_chain
    FROM TABLE(get_object_references(
        DATABASE_NAME=>'prod1',
        SCHEMA_NAME=>'PUBLIC',
        OBJECT_NAME=>'cplx_vw1'
    ))
    UNION ALL
    -- 递归层：遍历非TABLE类型的对象，继续获取其依赖
    SELECT
        v.dependency_level + 1,
        r.referenced_database_name,
        r.referenced_schema_name,
        r.referenced_object_name,
        r.referenced_object_type,
        CONCAT(r.referenced_database_name, '.', r.referenced_schema_name, '.', r.referenced_object_name),
        CONCAT(v.dependency_chain, ' -> ', CONCAT(r.referenced_database_name, '.', r.referenced_schema_name, '.', r.referenced_object_name))
    FROM view_dependencies v
    JOIN TABLE(get_object_references(
        DATABASE_NAME=>v.referenced_database_name,
        SCHEMA_NAME=>v.referenced_schema_name,
        OBJECT_NAME=>v.referenced_object_name
    )) r ON 1=1
    WHERE v.referenced_object_type != 'TABLE' -- 仅对非表对象继续递归
)
-- 最终筛选出所有底层表，或去掉WHERE查看完整依赖链
SELECT
    dependency_level,
    full_object_path,
    referenced_object_type,
    dependency_chain
FROM view_dependencies
WHERE referenced_object_type = 'TABLE'
ORDER BY dependency_level, full_object_path;

代码说明 #

• 递归CTE结构：通过UNION ALL连接初始层和递归层，自动遍历所有嵌套视图的依赖
• 层级追踪：用dependency_level记录当前依赖的深度，dependency_chain保留完整的依赖路径
• 终止条件：当遇到TABLE类型的对象时停止递归，最终结果只输出底层表

输出示例表格 #

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