Kamlesh's learning and rendezvous with the technical world

Menu

Drop tables for a user (A Workaround)

I am not an expert in database and hence i got stuck when I had to drop around hundreds of tables for a user in a database (Oracle 10g in my case). The tables were having complicated relationships and hence they were having dependencies on other tables maintained by constraints. But then I got a very nice trick to do the same. Below mentioned are the steps for the same:

Log on to the database as the user whose tables has to be dropped and run the following command,

select ‘drop table ‘ || tname || ‘cascade constraints;’ from tab ;

The output of this script will be the list of drop table commands which will also drop the constraints. For example if there are two tables say customer and account, the output will be like:

drop table customer cascade constraints;

drop table account cascade constraints;

Now the script for dropping all the tables is ready. This script will drop all the tables. However this script could be hand-coded but if you have hundreds of tables this trick will help you. You can also modify the command to write the output in a .sql file so that you can directly execute it.

However this is of course a workaround but this had solved my problem.