Snowflake Scripting in SQL – how to iterate over the results of a SHOW command?

I’m checking out the new SQL Snowflake Scripting (in preview), and I can’t figure out how to iterate over the results of a SHOW command — especially as some columns are lower cased.

• https://docs.snowflake.com/en/developer-guide/snowflake-scripting/
• https://hoffa.medium.com/sql-scripting-live-in-snowflake-288ef8c272fa

Answer

You can use this example as a template to iterate over results of SHOW:

declare
  res RESULTSET default (show warehouses);
  vw_cursor CURSOR for res;
  vw_table RESULTSET ;
begin
  for vw in vw_cursor do
    vw_table := (execute immediate 'show parameters like ' || '''STATEMENT_TIMEOUT_IN_SECONDS''' || ' in warehouse ' || vw."name");
    return TABLE(vw_table) ;
  end for;
end;

First, note that you can get the resultset from show in the declare part – that makes life easy.

Then you can have a cursor to go over each row with for vw in vw_cursor do.

Then you can execute immediate other queries, but make sure to quote with " to get the lower-cased columns like in vw."name".