Please I am trying to create a proc or function that will loop over databases in snowflake and retur-CodePudding

  **I was helped with this code in my earlier post but it still did not work, instead it returns the default snowflake table like 7 times on one call.**

create or replace procedure FIND_EMPTY_TABLES(DATABASE_PATTERN string) returns variant language javascriptexecute as owner as $$ class Account {constructor(databases){this.databases = databases;}} class Database {constructor(name) {this.name = name;}} class Query{constructor(statement){this.statement = statement;}} var account = getDatabasesInAccount(DATABASE_PATTERN); var out = []; for (var i = 0; i < account.databases.length; i ) { out = out.concat(rsToJSON(getQuery(select TABLE_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_OWNER,ROW_COUNT from ${account.databases[i].name}.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and ROW_COUNT = 0))); } return out; //------ function getQuery(sql){ cmd1 = {sqlText: sql}; var query = new Query(snowflake.createStatement(cmd1)); query.resultSet = query.statement.execute(); return query; } function executeSingleValueQuery(columnName, queryString) { cmd = {sqlText: queryString}; stmt = snowflake.createStatement(cmd); var rs; rs = stmt.execute(); rs.next(); return rs.getColumnValue(columnName); } function getDatabasesInAccount(databasePattern){ const SYSTEM_DB_NAMES = ["SNOWFLAKE", "SNOWFLAKE_SAMPLE_DATA"]; var db = executeSingleValueQuery("name", "show databases"); var i = 0; var dbRS = getResultSet(select DATABASE_NAME from "${db}".INFORMATION_SCHEMA.DATABASES where rlike (DATABASE_NAME, '${databasePattern}');`); var databases = []; var db; while (dbRS.next()){ db = new Database(dbRS.getColumnValue("DATABASE_NAME")); if (!SYSTEM_DB_NAMES.includes(db)) { databases.push(db); } } return new Account(databases); } function getResultSet(sql){ let cmd = {sqlText: sql}; let stmt = snowflake.createStatement(cmd); let rs = stmt.execute(); return rs; }

function rsToJSON(query) {
var i;
var row = {};
var table = [];
while (query.resultSet.next()) {
    for(col = 1; col <= query.statement.getColumnCount(); col  ) {
        row[query.statement.getColumnName(col)] = query.resultSet.getColumnValue(col);
    }
        table.push(row);
    }
    return table;
}
$$;

CodePudding user response：

All you need to do is read the SNOWFLAKE.ACCOUNT_USAGE.TABLES view where ROW_COUNT = 0

CodePudding user response：

You can make use of the ACCOUNT_USAGE view TABLES:
https://docs.snowflake.com/en/sql-reference/account-usage/tables.html

SELECT TABLE_CATALOG, TABLE_NAME 
FROM "SNOWFLAKE"."ACCOUNT_USAGE"."TABLES" 
WHERE ROW_COUNT = 0

But you should be aware that there could be some latency.