mmc18 mmc18 - 1 year ago 62
SQL Question

DROP all tables starting with "EXT_" in Oracle SQL

I know this question may ask many times but I could not find one line SQL statement.
I remember I did it before but now I could not remember how I did

I want to drop all tables whose name starts with "EXT_". Is it possibile to make it happen with one line SQL statement.

tvm tvm
Answer Source

You could use a short anonymous block to do this.

  FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'EXT_%' )
    EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name;