Q
Manage Learn to apply best practices and optimize your operations.

How to drop all tables from database with one SQL query

How to drop all tables from the database using one SQL query?

How to drop all the tables using one SQL query?

There is no DROP ALL TABLES command. So this cannot be done with one SQL statement. The easiest way to do this is to generate your DROP TABLE commands on the fly, similar to the following:

SPOOL drop_tables.sql
SELECT 'DROP TABLE '||table_name||';'
FROM user_tables;
SPOOL OFF
@drop_tables

The drop_tables.sql script is dynamically generated for all tables in your schema.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

Thing's wouldn't be that easy in real life...

Tables usually have foreign key constraints, and those constraints will not let you drop the referenced tables. To achieve something like that, the script would need to drop all foreign key constraints first, or determine the correct order for dropping the tables without raising errors because of them.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close