--删除外键约束
select 'Alter table '||TABLE_NAME||' '||'drop constraint'||' '|| CONSTRAINT_NAME||';'
from user_constraints
where CONSTRAINT_TYPE ='R'
and TABLE_NAME like 'TA/_%' escape '/';
--删除表名
select 'drop table '||TABLE_NAME||' '||';'
from user_tables
where TABLE_NAME like 'TA/_%' escape '/';
--------------------------下面更好一点 --------
declare
v_sql varchar2(4000);
cursor cur1 is
select 'Alter table ' || TABLE_NAME || ' ' || 'drop constraint' || ' ' ||
CONSTRAINT_NAME || ';'
from user_constraints
where CONSTRAINT_TYPE = 'R'
and TABLE_NAME like 'TA/_%' escape '/';
cursor cur2 is
select 'drop table ' || TABLE_NAME || ' ' || ';'
from user_tables
where TABLE_NAME like 'TA/_%' escape '/';
begin
open cur1;
loop
fetch cur1
into v_sql;
exit when cur1%notfound;
execute immediate v_sql;
end loop;
close cur1;
open cur2;
loop
fetch cur2
into v_sql;
exit when cur2%notfound;
execute immediate v_sql;
end loop;
close cur2;
end;