Snowflake scripting

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

Johan

— 1 min read

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

Read more