Drop all tables in Oracle DB (Schema)

DB Scripts

Imagine you have a list of tables in your Oracle DB and you want to drop them all using a client like SQLDeveloper. That’s easier said then done, as you have to drop each table individually.

Therefore, a SQL query that generates a “DROP-sql-script”:

SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;
  • user_tables is a system table which contains all the tables of the user
  • the SELECT clause will generate a DROP statement for every table

This will generate something like:

DROP TABLE "FOO" CASCADE CONSTRAINTS;                    
DROP TABLE "BAR" CASCADE CONSTRAINTS;         
DROP TABLE "DUMMY" CASCADE CONSTRAINTS;         
...

Now you can easily run that script 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *