Q
Problem solve Get help with specific problems with your technologies, process and projects.

Query to delete all tables

What is the query to delete all tables?
If you want to delete tables from your schema you can create a procedure that will disable all constraints first, then delete all the rows (or truncate for that matter for better performance) and then enable the constraint. Now, you want to do it dynamically and you want to specify in the procedure to execute it for the user's schema (AUTHID CURRENT_USER) and not for the procedure owner's schema.

CREATE OR REPLACE PROCEDURE delete_all_tables AUTHID CURRENT_USER IS

 
    cursor c1 (p_enable_or_disable in varchar2) is
              select 'alter table ' || table_name  || ' ' || p_enable_or_disable
               ||  ' constraint ' || constraint_name vsql_str
                   from user_constraints
                 where r_constraint_name is not null;

   cursor c2 is select 'truncate table ' || table_name vsql_str2 from user_tables;


begin

    /* Dynamically disable all constraint */

    for c1rec in c1 ('DISABLE') loop
     execute immediate c1rec.vsql_str;
    end loop;

   /* Truncate or Delete all tables */

   for c2rec in c2 loop
    execute immediate c2rec.vsql_str2;
   end loop;

   /* Enable constraints */

   for c1rec in c1 ('ENABLE') loop
     execute immediate c1rec.vsql_str;
   end loop;


end; 
This was last published in January 2005

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close