Todays Favorite Little SQL Gem

Max Rydahl Andersen

I've always been annoyed by the fact I can't just do something like "drop all tables cascade constraints" or similar in my RDBMS....well, while I've been messing around with some DDL/rev.eng. code where it cames in handy I wanted such a feature for Oracle.

I don't remember where I got it from orginally, but here it goes:

select 'drop ' || object_type || ' "' || object_name || '"'
|| decode(object_type,
'CLUSTER', ' including tables cascade constraints;',
'TABLE', ' cascade constraints;', ';')
from user_objects
where object_type in (
) and object_name not like 'BIN$%';

The script is simple to use, execute it, capture its output and execute that...


P.S. Notice the "not like 'BIN$%', that is because of Oracle 10's painfull recycle bin isn't "dropable" like everything else - and please don't get me started about their lack of JDBC metadata support for identifying them.