CODEWITHSUNDEEP
snowflake-cloud-data-platform
Richard_2413
Richard_2413

Reputation: 119

Drop multiple tables at once

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?

Upvotes: 7

Views: 9640

Answers (3)

user16735583
user16735583

Reputation: 96

If this is a situation where all the tables you want to drop are in the same schema, then you can simply drop the schema if you'd rather not write out every table name

DROP SCHEMA my_database.my_schema CASCADE

Upvotes: 2

Eric Lin
Eric Lin

Reputation: 1530

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"] |
+---------------------------------------------------------------------------------------------+

Upvotes: 3

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1616

Hi you can do a 2 step like this where you generate the DDL if you have the list of tables to be deleted.

SELECT 'DROP TABLE ' || table_name || ';' 
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE IN ('TAB1', 'TAB2');

Upvotes: 6

Related Questions