How do i drop multiple tables

Analytics

How do i drop multiple tables

I am new to the treadata environment, and would like to delete tables in my work environment (temporary work space)
For example
drop table s_mrktng.cletang_1486386770;
drop table s_mrktng.cletang_1486409292;
drop table s_mrktng.cletang_1486409363;
drop table s_mrktng.cletang_1486409456;
drop table s_mrktng.cletang_1486409527;

but instead of type the file name over and over can i do
drop table s_mrktng.cletang_1486* ;

If * is not the wild card is there one that i could use.

Thanks

3 REPLIES
Enthusiast

Re: How do i drop multiple tables

There's a quick trick which DBAs normally use to generate all those drop statements.

SELECT 'DROP TABLE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';'
FROM DBC.TABLES
WHERE DATABASENAME='s_mrktng'
AND TABLEKIND = 'T'
AND TABLENAME LIKE 'cletang_1486%';

Then copy paste and run those outputs.

Of course some get further creative by writing scripts to export the above output via bteq and run it again from bteq and so on ...

but I think this is sufficient to serve your purpose.
Enthusiast

Re: How do i drop multiple tables

This worked really well in my case where I had to delete several tables created my me in a specific database and I didnt want to write multiple queries to drop all tables one by one.

Re: How do i drop multiple tables

Use TABLESV in the event you have table names > 30 chars.

 

SyntaxEditor Code Snippet

SELECT 'DROP TABLE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';'
FROM DBC.TABLESV
WHERE DATABASENAME='DATABASE_NAME'
AND TABLEKIND = 'T'
AND TABLENAME LIKE 'TABLE_PREFIX_%';