Snowflake stored procedure to crawl across all the stages defined in the account and collect the number of files and total size for the given stage

stage_storage_crawler.sql

create or replace procedure stage_storage_crawler()
returns string
language javascript
EXECUTE AS CALLER
as     
$$

//create a temp table to hold the results
snowflake.execute( {sqlText:`
    create or replace temp table stage_storage_crawler_results(
        name string,
        database_name string,
        schema_name string,
        owner string,
        file_count int,
        size_bytes int
    )` } );

//get the list of stages in the account
var stage = snowflake.execute( {sqlText:`
    show stages in account` } );
    
while(stage.next()){
        var name = stage.getColumnValue(2);
        var db = stage.getColumnValue(3);
        var schema = stage.getColumnValue(4);
        var owner = stage.getColumnValue(8);
        
        var count = 0;
        var size = 0;
        try{ //in the event the caller doesn't have rights to list the stage - will gen a result but the count and size will both be -1
            var files = snowflake.createStatement( { sqlText: `ls @` + db + `.` + schema + `.` + name } ).execute();
            while(files.next()){
                count++;
                size += files.getColumnValue(2);
            }
        }
        
        catch(err){
            //set count and size to -1 to indicate an access error
            count = -1;
            size = -1;
        }
        
        name = '\'' + name + '\'';
        db = '\'' + db + '\'';
        schema = '\'' + schema + '\'';
        owner = '\'' + owner + '\'';
        
        snowflake.createStatement( { sqlText: `insert into stage_storage_crawler_results values (`  
            + name + `,`
            + db + `,`
            + schema + `,`
            + owner + `,`
            + count + `,`
            + size + `)` } ).execute();
        
    } 
    
    return "done!"
    
$$;