Tuesday, July 24, 2012

Drop all DB objects - Oracle

Created this for future reference. This SQL goes and drops all user objects in the current DB, great when messing with local Oracle XS DB
declare

v_str1 varchar2(200) := null;

cursor get_sql is select 'drop '||o.object_type||' '|| o.object_name|| DECODE(o.OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS PURGE') v_str1
from user_objects o
where o.object_type in ('TABLE','VIEW','PACKAGE','TYPE','PROCEDURE','FUNCTION','TRIGGER','SEQUENCE') order by object_type,object_name;

begin

open get_sql;

loop

fetch get_sql into v_str1;
if get_sql%notfound
then exit;
end if;
execute immediate v_str1;
end loop;
close get_sql;
end;

Popular Posts

Followers