Snowflake Scripting - EXECUTE IMMEDIATE multiple statements to drop tables without loop/cursor

Question

The goal is go drop multiple tables that matches a specific naming pattern using only SQL without cursors/loops:

Setup:

CREATE TABLE tab_1(i INT);
CREATE TABLE tab_2(i INT);
CREATE TABLE tab_3(i INT);
CREATE TABLE tab_4(i INT);
CREATE TABLE tab_5(i INT);

Normally it could be done by generating query first:

SELECT LISTAGG(CONCAT('DROP TABLE IF EXISTS ', TABLE_NAME, ';', CHAR(10)) ,'')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_SCHEMA = 'PUBLIC'
  AND TABLE_NAME ILIKE 'TAB!_%' ESCAPE '!'
GROUP BY GROUPING SETS(());
/*
DROP TABLE IF EXISTS TAB_1; 
DROP TABLE IF EXISTS TAB_2; 
DROP TABLE IF EXISTS TAB_3; 
DROP TABLE IF EXISTS TAB_4; 
DROP TABLE IF EXISTS TAB_5; 
*/

Then the output needs to be copied and run. This manual step is unecessary and should be avoided.

The idea was to use SELECT INTO and EXECUTE IMMEDIATE to invoke generated query:

DECLARE 
    QUERY STRING;
BEGIN
    SELECT LISTAGG(CONCAT('DROP TABLE IF EXISTS ', TABLE_NAME, ';', CHAR(10)), '')
    INTO :QUERY
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
      AND TABLE_SCHEMA = 'PUBLIC'
      AND TABLE_NAME ILIKE 'TAB!_%' ESCAPE '!'
    GROUP BY GROUPING SETS(());

  QUERY := COALESCE(QUERY, 'SELECT ''No tables found''');

  EXECUTE IMMEDIATE :QUERY;
  RETURN :QUERY;
END;

This is not possible:

Multiple SQL statements in a single API call are not supported; use one API call per statement instead.

Answer 1

The answer is to wrap generated code with BEGIN END block:

DECLARE 
    QUERY STRING;
BEGIN
    SELECT CONCAT('BEGIN', CHAR(10)
             ,LISTAGG(CONCAT('DROP TABLE IF EXISTS ', TABLE_NAME, ';', CHAR(10)), '')
             ,'END')
    INTO :QUERY
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
      AND TABLE_SCHEMA = 'PUBLIC'
      AND TABLE_NAME ILIKE 'TAB!_%' ESCAPE '!'
    GROUP BY GROUPING SETS(());

  QUERY := COALESCE(QUERY, 'SELECT ''No tables found''');

  EXECUTE IMMEDIATE :QUERY;
  RETURN :QUERY;
END;

Output:

BEGIN
DROP TABLE IF EXISTS TAB_1;
DROP TABLE IF EXISTS TAB_2;
DROP TABLE IF EXISTS TAB_3;
DROP TABLE IF EXISTS TAB_4;
DROP TABLE IF EXISTS TAB_5;
END

SHOW TABLES LIKE 'TAB_%';
-- no resultset

And second run: