Snowflake遍历所有数据库列出零行空表的存储过程实现求助

原代码核心问题 #

• 仅查询当前会话所在数据库的INFORMATION_SCHEMA，未遍历所有有权限访问的数据库
• SQL语句未用引号包裹，存在语法错误
• 仅取结果集第一行数据，未返回全部空表结果
• 存在多余无效代码片段
• 未处理跨库查询逻辑，无法获取全库表数据

可行存储过程代码 #

CREATE OR REPLACE PROCEDURE CHECK_EMPTY_TABLES()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER -- 用调用者权限查询，避免权限不足
AS
$$
// 存储返回结果的数组
let emptyTables = [];
try {
    // 第一步：查询当前账号有权限访问的所有数据库
    let dbStmt = snowflake.createStatement({sqlText: "SELECT DATABASE_NAME FROM INFORMATION_SCHEMA.DATABASES WHERE DELETED IS NULL"});
    let dbRs = dbStmt.execute();
    
    // 遍历每个数据库
    while (dbRs.next()) {
        let dbName = dbRs.getColumnValue(1);
        // 过滤系统库，不需要过滤可删除此行
        if (dbName === 'SNOWFLAKE' || dbName === 'SNOWFLAKE_SAMPLE_DATA') continue;
        
        let tableQuery = `SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ROW_COUNT 
                          FROM ${dbName}.INFORMATION_SCHEMA.TABLES 
                          WHERE ROW_COUNT = 0 
                          AND TABLE_TYPE = 'BASE TABLE' -- 排除视图，只查物理表，不需要可删除此行
                          AND DELETED IS NULL`;
        let tableStmt = snowflake.createStatement({sqlText: tableQuery});
        let tableRs = tableStmt.execute();
        
        // 遍历当前库的所有空表
        while (tableRs.next()) {
            emptyTables.push({
                数据库: tableRs.getColumnValue(1),
                模式: tableRs.getColumnValue(2),
                表名: tableRs.getColumnValue(3),
                行数: tableRs.getColumnValue(4)
            });
        }
    }
    // 以JSON格式返回所有空表结果
    return {
        执行状态: "成功",
        空表总数: emptyTables.length,
        空表列表: emptyTables
    };
} catch (err) {
    return {
        执行状态: "失败",
        错误信息: err.message.replace(/\n/g, " ")
    };
}
$$;

使用方法 #

• 执行上述代码创建存储过程
• 调用存储过程：CALL CHECK_EMPTY_TABLES();
• 返回结果为JSON格式，可直接查看所有空表信息

注意事项 #

• INFORMATION_SCHEMA中的ROW_COUNT是元数据统计值，存在最多几小时的延迟，如果需要实时准确行数，可以修改逻辑对每张表执行SELECT COUNT(*)校验，数据量较大时会增加查询耗时
• 存储过程默认使用调用者权限执行，仅能查询当前账号有权限访问的数据库和表

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