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

Another way to drop all tables

There is a second solution to drop all tables without creating a script. You could set up a cursor reading all the tables, and you could use EXECUTE IMMEDIATE to drop them within a loop.

This is a message to Brian Peasland: In the Ask The Oracle Expert section of SearchOracle.Com, the question " How to drop all the tables using one SQL query?" was posed with your answer. There is a second solution to drop all tables without creating a script. I'm not saying this is the best thing to do, but it is another approach. You could set up a cursor reading all the tables, and you could use EXECUTE IMMEDIATE to drop them within a loop. I could follow up with an example, if necessary.
As with many things, there is more than one way to accomplish the same task. You could certainly write a PL/SQL block to open a cursor and use the EXECUTE IMMEDIATE command to drop all of the tables.
DECLARE
   CURSOR c1 IS SELECT table_name FROM user_tables;
   t_name USER_TABLES.TABLE_NAME%TYPE;
   drop_stmt VARCHAR2(100);
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO t_name;
      EXIT WHEN c1%NOTFOUND;
      drop_stmt := 'DROP TABLE '||t_name;
      EXEC IMMEDIATE drop_stmt;
   END LOOP;
END;
/
So the above will accomplish the same task as my previous response. But my question to you is, which solution is easier to implement? For the Oracle newbie, my solution is not only shorter, but more intuitive, especially if they do not know PL/SQL. Obviously, the PL/SQL solution is useful if the requirement is to develop a stored procedure that will implement this functionality, but that was not part of the original poster's question.

Dig Deeper on Oracle and 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.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

The drop table syntax needs to be modified - u use 'drop table '||table_name||' cascade constraints' as a table cannot get dropped if it has dependencies.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close