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;
/
The quick and dirty solution would be to do something like
FOR x IN (SELECT * FROM user_tables)
LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || x.table_name ||
' CASCADE CONSTRAINTS';
EXCEPTION
WHEN others THEN
dbms_output.put_line( 'Failed to drop ' || x.table_name );
END;
END LOOP;
and run that a number of times until all the tables had been dropped. This will take multiple passes because you can't drop a parent table while there are still child tables with foreign keys that reference the parent.
The cleaner option would be to write a hierarchal query against the data dictionary to get the child tables, the parents of those children, the grandparents, etc. and to walk the tree to drop the appropriate objects. That should avoid errors but it would require a bit more work to code.
execute immediate - pass in the generated string
It is generally more efficient when dropping tables to use the truncate statement.
You can execute dynamic scripts using the execute immediate command
