How to Delete all tables, views, constraints and sequences in Oracle SQL :

BEGIN 
--Bye Views!  
FOR i IN (SELECT view_name FROM USER_VIEWS) LOOP 
    EXECUTE IMMEDIATE 'drop view'|| i.view_name ||'';  
END LOOP;  
--Bye Sequences! 
FOR i IN (SELECT sequence_name FROM USER_SEQUENCES) LOOP
    EXECUTE IMMEDIATE 'drop sequence '|| i.sequence_name ||''; 
END LOOP; 

--Bye Constraints!  
FOR i IN (SELECT table_name, constraint_name FROM USER_CONSTRAINTS WHERE constraint_type='r') LOOP 
    EXECUTE IMMEDIATE 'alter table '|| i.table_name ||' DROP CONSTRAINT '||i.constraint_name; 
--Bye Tables! 
FOR i IN (SELECT table_name FROM USER_TABLES) LOOP
    EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS '; 
END LOOP; 

END;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s