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 (
'CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM', 'FUNCTION',
'PROCEDURE', 'PACKAGE'
) and object_name not like 'BIN$%';
The script is simple to use, execute it, capture its output and execute that...
Cheers
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.