Does anybody know how to run all the lines generated from the following query as scripts on their own right?
select 'DROP TABLE '||table_name||' CASCADE CONSTRAINTS;' from user_tables;
What I'm basically trying to do, is delete all the user tables and constraints on my DB (this is oracle). The output I get is correct, but I want to know how I would run all the lines without copy/pasting.
Also, is there a more efficient way to drop all tables (including constraints)?
begin
for i in (select table_name from user_tables)
loop
execute immediate ('drop table ' || i.table_name || ' cascade constraints');
end loop;
end;
/
Justin Cave brought up an excellent point - the following will drop tables within the user's schema starting at the outermost branches of the hierarchy of dependencies, assuming all foreign keys reference the primary key, not a unique constraint. Tables without primary keys would be dropped last.
begin
for i in (select parent_table, max(tree_depth) as tree_depth
from (select parent.table_name as parent_table,
child.constraint_name as foreign_key,
child.table_name as child_table,
LEVEL AS TREE_DEPTH
from (select table_name, constraint_name
from USER_constraints
where constraint_type = 'P'
) parent
LEFT JOIN
(SELECT TABLE_NAME, CONSTRAINT_NAME,
r_constraint_name
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R') child
on parent.constraint_name =
child.r_constraint_name
CONNECT BY NOCYCLE
(PRIOR CHILD.TABLE_NAME = PARENT.TABLE_NAME)
UNION
select DT.table_name as parent_table,
NULL AS FOREIGN_KEY, NULL AS CHILD_TABLE,
0 AS TREE_DEPTH
FROM USER_TABLES DT
WHERE TABLE_NAME NOT IN
(SELECT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P')
)
group by parent_table
order by 2 desc
)
loop
execute immediate ('drop table ' || i.parent_table ||
' cascade constraints');
end loop;
end;
/