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 🙂
