11. October 2009 · 6 comments · Categories: Code, Oracle · Tags: , ,

This script would create a drop table script for all the tables

-- Drop All Tables in a schema:
-- Drop_Tables.sql - Creates script to drop all tables and views from schema
--  Use: script reads table list from schema and spools to a new script
--       containing statements used to drop each table and view.
--       That script is called and drops the schema tables and views on the list.
Spool drop_schema_tables.sql
select 'Drop ' || object_type || ' ' || object_name || ';' from user_objects
where object_type in ('TABLE','VIEW')
ORDER BY object_type DESC, object_name;
spool off
-- @drop_schema_tables.sql


  1. Tom Hoody says:

    I never thought of it this way. It’s a lot easier than I thought.

    Thank you. Your tip is awesome! :)

  2. Rajagopal.C says:

    Hi How to write simple coding in drop table with oracle

  3. Jason Huang says:

    drop table [table name];

  4. srikanth gundoju says:

    this made my job so easy.. good one.. thanks

  5. Anonymous says:

    very good

  6. John Rambo says:

    awesome….works absolutely fine

Leave a Reply

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