Re: How to DROP ALL TABLEs?

From: Chris Hamilton <toneczar_at_erols.com>
Date: Fri, 15 May 1998 11:46:38 GMT
Message-ID: <6jh9v3$afb$2@goo.nwd.usace.army.mil>

Troy Perchotte <perchott_at_nospamdigitalcourier.com> wrote:

>How do I drop all of the tables of a schema, without having to remove
>the schema itself?
>
>ie: drop table demo.* cascade constraints; - doesn't work.

Here's a SQL*Plus script I wrote to drop the tables, views, and procedures for a schema. Modify it to suit your needs.

• drop_em.sql
• Chris Hamilton
• 16-Mar-1998
• SQL*Plus script to drop all RMS tables, in
• preparation for re-importing.

set pagesize 0;
set linesize 79;
set trimspool on;
set feedback off;
set echo off;

spool drop_em.tmp;

select 'drop ' || lower(object_type) || ' ' || object_name || ';' txt
from dba_objects
where object_type in ('TABLE', 'VIEW', 'PROCEDURE') and owner = upper('&owner')
order by decode(object_type, 'VIEW', 1, 'PROCEDURE', 2, 'TABLE', 3, 4),

         object_name;

spool off;

set feedback on;

set echo on;

@drop_em.tmp

alter tablespace rms_data coalesce;
alter tablespace rms_index coalesce;
alter tablespace rms_library coalesce;

set echo off;
set linesize 79;
set pagesize 14;

Chris

---

Chris Hamilton -- toneczar_at_erols.com
City of Washington Pipe Band
http://www.serve.com/cowpb/chamilton.html Received on Fri May 15 1998 - 06:46:38 CDT