oracle mass table drop

Sometimes I need to build a small script to delete a huge set of tables of a schema.

In Oracle is easy to do it, using the meta tables "all_tables", "all_views", and "all_sequences":

select  'drop table ' || table_name || ' cascade constraints;' 
   from all_tables where owner='protoss' and  table_name not like 'BIN%';

select  'drop view ' || view_name || ' ;'
   from all_views where owner='zerg' ;
 
  select  'drop sequence ' || sequence_name || ' ;'
   from all_sequences where sequence_owner='spacemarine' ;

The "BIN%" table are special temp tables of oracle.

On PostgreSQL, try use PG_TABLES meta-table

Happy sqlizing!

Leave a Reply