Thursday, May 31, 2012

Oracle Drop All Tables, Views, Sequences, Triggers






1. Drop All Views




BEGIN
FOR i IN (SELECT view_name FROM user_views)
LOOP
EXECUTE IMMEDIATE('DROP VIEW ' || user || '.' || i.view_name);
END LOOP;
END;

2. Drop All Triggers

BEGIN
FOR i IN (SELECT trigger_name FROM user_triggers)
LOOP
EXECUTE IMMEDIATE('DROP TRIGGER ' || user || '.' || i.trigger_name);
END LOOP;
END;

3. Drop All Sequences

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

4. Drop All Tables

BEGIN
FOR i IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i._table_name);
END LOOP;
END;

2 comments:

  1. Thanks for the post; really helpful.

    One remark, for Oracle 11g XE - when dropping tables, the correct syntax is:

    'DROP TABLE ' || user || '.' || i.table_name

    Notice the missing "_" char before "table_name".

    Cheers!

    ReplyDelete
    Replies
    1. Thanks for the note about Orale 11g XE. Couldn't get my hands on one to check it out.

      Delete