Snowflake scripting

Johan
1 min readApr 24, 2024

--

This is really nice. Build blocks of code that can be used in stored procedures or tasks but also in the console.

I will build a script where I will be dropping tables and views in a schema.

EXECUTE IMMEDIATE $$

DECLARE
v_sql_query VARCHAR;
c_table CURSOR FOR SELECT TABLE_NAME FROM DB_NAME.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'SCH_NAME';

BEGIN
v_sql_query := '';

FOR record IN c_table DO
v_sql_query := 'DROP TABLE DB_NAME.SCH_NAME.' || record.TABLE_NAME;
EXECUTE IMMEDIATE v_sql_query;
END FOR;

END
$$;

What happens here:

  • A cursor is filled with the results of he TABLES table, representing all tables in my database/schema
  • The cursor is looped for all its occurences
  • In the cursor, a SQL statement is composed, dynamically putting the table_name in there
  • The SQL statement is executed

One final remark: if you want to put this in a stored procedure, the syntax is slightly different. We will cover that part in future posts. For now, happy Snowflake scripting.

Snowflake Scripting Developer Guide | Snowflake Documentation

--

--

Johan

I am a freelance data engineer. My main focus is Snowflake, but I am always eager to learn new things. Python and AWS are my main side interests