Drop multiple tables at once

Question

In SQL you have the ability to drop multiple tables at once with a simple query like: drop table a, b, c

In Snowflake this doesnt work. Is there a way to drop multiple tables at once?

Answer 1

Maybe you can create a simple SP to do this for you:

create or replace procedure drop_tables(list varchar)
returns string
language javascript
as 
$$
    var l = LIST.split(',');
    var sqls = [];
    for (i=0; i<l.length; i++) {
        var sql = "DROP TABLE IF EXISTS " + l[i].trim();
        var rs = snowflake.execute( {sqlText: sql});
        
        sqls.push(sql);
    }
    
    return JSON.stringify(sqls);
$$;

call drop_tables('mytest,test , my_table ');

+---------------------------------------------------------------------------------------------+
| DROP_TABLES                                                                                 |
|---------------------------------------------------------------------------------------------|
| ["DROP TABLE IF EXISTS mytest","DROP TABLE IF EXISTS test","DROP TABLE IF EXISTS my_table"] |
+---------------------------------------------------------------------------------------------+