Amazon Virtual Private Cloud ID for external stage, external function, and external volume¶

USE ROLE ACCOUNTADMIN;

SELECT stage_url, stage_region, stage_owner, stage_catalog, stage_schema
  FROM SNOWFLAKE.ACCOUNT_USAGE.STAGES
  WHERE STARTSWITH(stage_url, 's3')
    AND stage_url IS NOT NULL
    AND deleted IS NULL;

USE ROLE ACCOUNTADMIN;

SELECT function_name, function_definition, function_owner, function_catalog, function_schema
  FROM SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS
  WHERE function_language = 'EXTERNAL'
    AND function_definition ILIKE '%.execute-api.%.amazonaws.com%'
    AND deleted IS NULL;

use role accountadmin;

DECLARE
res1 RESULTSET;
res2 RESULTSET;
sql_vol VARCHAR;
rpt VARIANT;
rpt_int VARIANT;
BEGIN
  rpt := object_construct();
  sql_vol := 'SELECT PROPERTY, VALUE:"NAME"::VARCHAR as NAME, VALUE:"STORAGE_ALLOWED_LOCATIONS"::VARCHAR as S3_PATH FROM (
SELECT PARSE_JSON(T."property_value") AS VALUE, T."property" as PROPERTY
FROM TABLE(RESULT_SCAN(last_query_id())) T
WHERE T."property_type" = \'String\'
AND T."property" != \'ACTIVE\'
AND VALUE:"STORAGE_PROVIDER"=\'S3\')
;';
  show external volumes;
  LET c1 CURSOR FOR SELECT * FROM TABLE(RESULT_SCAN(last_query_id()));
  OPEN c1;
  FOR record IN c1 DO
    res1 := (execute immediate 'describe external volume ' || record."name");
    res2 := (execute immediate :sql_vol);
    rpt_int := object_construct();
    let c2 CURSOR for res2;
    open c2;
    for inner_record in c2 do
        rpt_int := object_insert( rpt_int, inner_record.NAME, inner_record.S3_PATH);
    end for;

    rpt := object_insert( rpt, record."name", rpt_int );
  END FOR;
  RETURN rpt;
END;