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