Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Thursday, July 24, 2008

Drop All Tables in a Single Query

Recently, I needed to find a single, simple PL/SQL command to drop all the tables from an Oracle database without explicitly naming each table and without dropping the entire schema. Application development has just started, and I need to easily rename tables and other database objects, so the simplest solution at this early stage is often just to wipe and recreate the entire schema.
One possible solution would be to login as SYS and drop the schema itself, but I need to configure a single login for any database access, i.e., the user whose database objects will be deleted.

I did not find my specific solution online, but I owe thanks to alternative approaches in some other posts.

First, make sure that you connect to Oracle as the correct user, i.e., the one whose tables will be dropped.

This command will drop all the user's tables:
BEGIN 
  FOR i IN (SELECT table_name FROM user_tables) 
    LOOP 
      EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i.table_name || ' CASCADE CONSTRAINTS'); 
    END LOOP
END;

Similar commands can be used to drop all triggers, sequences, etc.  For example,
BEGIN 
  FOR i IN (SELECT trigger_name FROM user_triggers) 
    LOOP 
      EXECUTE IMMEDIATE('DROP TRIGGER ' || user || '.' || i.trigger_name); 
    END LOOP
END;

               and

BEGIN 
  FOR i IN (SELECT sequence_name FROM user_sequences) 
    LOOP 
      EXECUTE IMMEDIATE('DROP SEQUENCE ' || user || '.' || i.sequence_name); 
    END LOOP
END;

NB: if you drop all tables before triggers, your triggers will be renamed with special characters and the command might not work; make sure to drop triggers before tables.