Re: How to drop all views or tables?

In article <1992Oct27.193408.5234_at_gdstech.grumman.com>, un_at_gdstech.grumman.com (Un Fu) writes:
|> Does anyone know how to drop all views or tables belong
|> to a particular user by the particular user (say me)?
|> The 'DROP VIEW ...' and 'DROP TABLE ...' commands can
|> only remove one view or one table at a time.
|>

I use the following script through SQLPLUS to drop all tables and views owned by a user (the user you are conected as). Indices will be removed when the tables they reference are removed.

Don't ask about removing the username :-)

• DROPALL.SQL (unix-based) -------------------------

set heading off
set pagesize 0
set feedback off

select 'drop ' || object_type || ' ' || object_name || ';' from user_objects
where object_type != 'INDEX'

spool /tmp/drop.lis
/
spool off

set termout off
start /tmp/drop.lis
host rm /tmp/drop.lis
set termout on
exit

• DROPALL.SQL (vax-based) ------------------------------

   set heading off
   set pagesize 0
   set feedback off
   set verify off

   select 'DROP ' || object_type || ' ' || object_name || ';'    from user_objects
   where object_type != 'INDEX'

   spool dropall.lis
   /
   spool off

   set termout off
   start dropall.lis
   host del dropall.lis;*
   set termout on
   exit

  ^^^^^^^^^          Jack Jolly  _at_  jjolly_at_jjolly.b24b.ingr.com
 (| 0   - |)
  |   *   |             Intergraph Corporation, Huntsville AL
 \_______/                AEC Project Management Support