Snowflake中获取指定Schema下各视图源表信息的循环实现需求

方法一：用存储过程自动遍历并收集结果 #

下面的存储过程会自动遍历TEST库下BALLSSchema的所有视图，调用GET_OBJECT_REFERENCES获取每个视图的源表信息，最终将结果存入临时表方便查看：

CREATE OR REPLACE PROCEDURE GET_ALL_VIEW_SOURCE_TABLES()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    // 创建临时表存储最终结果
    var createTmpTable = `
        CREATE OR REPLACE TEMPORARY TABLE VIEW_SOURCE_TABLES (
            VIEW_DATABASE VARCHAR,
            VIEW_SCHEMA VARCHAR,
            VIEW_NAME VARCHAR,
            SOURCE_DATABASE VARCHAR,
            SOURCE_SCHEMA VARCHAR,
            SOURCE_TABLE VARCHAR,
            REFERENCE_TYPE VARCHAR
        )
    `;
    snowflake.execute({sqlText: createTmpTable});

    // 查询目标Schema下的所有视图
    var getViewsSql = `
        SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
        FROM TEST.INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = 'BALLS'
    `;
    var viewsResult = snowflake.execute({sqlText: getViewsSql});

    // 循环遍历每个视图，拉取源表信息并插入临时表
    while (viewsResult.next()) {
        var viewDb = viewsResult.getColumnValue(1);
        var viewSchema = viewsResult.getColumnValue(2);
        var viewName = viewsResult.getColumnValue(3);
        
        var insertSourceSql = `
            INSERT INTO VIEW_SOURCE_TABLES
            SELECT '${viewDb}', '${viewSchema}', '${viewName}',
                   REFERENCED_DATABASE_NAME, REFERENCED_SCHEMA_NAME, REFERENCED_OBJECT_NAME, REFERENCE_TYPE
            FROM TABLE(GET_OBJECT_REFERENCES(DATABASE_NAME=>'${viewDb}', SCHEMA_NAME=>'${viewSchema}', OBJECT_NAME=>'${viewName}'))
        `;
        snowflake.execute({sqlText: insertSourceSql});
    }

    return '执行完成，结果已存入临时表VIEW_SOURCE_TABLES';
$$;

使用步骤

1. 执行上述代码创建存储过程
2. 调用存储过程启动遍历：

CALL GET_ALL_VIEW_SOURCE_TABLES();

3. 查询临时表查看所有结果：

SELECT * FROM VIEW_SOURCE_TABLES;

方法二：动态生成SQL批量执行（无需存储过程） #

如果不想创建存储过程，可以先生成所有视图的查询语句，再批量执行：

-- 生成每个视图对应的源表查询语句
SELECT 'SELECT ''' || TABLE_CATALOG || ''' AS VIEW_DATABASE, ''' || TABLE_SCHEMA || ''' AS VIEW_SCHEMA, ''' || TABLE_NAME || ''' AS VIEW_NAME, REFERENCED_DATABASE_NAME, REFERENCED_SCHEMA_NAME, REFERENCED_OBJECT_NAME, REFERENCE_TYPE FROM TABLE(GET_OBJECT_REFERENCES(DATABASE_NAME=>''' || TABLE_CATALOG || ''', SCHEMA_NAME=>''' || TABLE_SCHEMA || ''', OBJECT_NAME=>''' || TABLE_NAME || '''));' AS SQL_STATEMENT
FROM TEST.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = 'BALLS';

将查询结果里的所有SQL语句复制出来批量执行，就能得到所有视图的源表信息。

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