How to drop all tables from database with one SQL query

How to drop all the tables using one SQL query?

    Requires Free Membership to View

    Login

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.

This was first published in October 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top