Thursday, March 17, 2016

Drop all tables in a schema


**********************************************************************
You can use this simple plsql block to drop all objects in your current schema.
**********************************************************************

declare
VSQL varchar2(4000);
OBJ_NAME varchar2(100);
OBJ_TYPE varchar2(100);
OBJ_OWNER varchar2(100);
cursor c1 is select object_type,object_name from user_objects where object_type in ('TABLE','VIEW');

begin

open c1;
loop
fetch c1 into OBJ_TYPE,OBJ_NAME;
exit when c1%NOTFOUND;
IF OBJ_TYPE='TABLE' 
THEN
VSQL:=' drop '||OBJ_TYPE||' '||OBJ_NAME||' cascade constraints';
DBMS_OUTPUT.PUT_LINE(VSQL);
execute IMMEDIATE VSQL;
ELSE  
VSQL:=' drop '||OBJ_TYPE||' '||OBJ_NAME;
DBMS_OUTPUT.PUT_LINE(VSQL);
execute IMMEDIATE VSQL;
END IF;
end loop;
close c1;
end;
/




Note :

Remove "where object_type in ('TABLE','VIEW')" to drop all objects from current user.
Or you can edit to include only type of objects you want to be dropped.

You can Alter OBJECT TYPE any of below:


'TABLE','VIEW','SYNONYM','SEQUENCE','PROCEDURE','TRIGGER'

No comments:

Post a Comment