mmc18 mmc18 - 5 months ago 14
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

You could use a short anonymous block to do this.

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