In this short article I will describe how I went about deleting multiple database tables quickly without using the DROP DATABASE dbName statement. Feel free to skip right to the solution if you like.

Background Story

As part of changing one of our sites over to a new server I had to delete the existing Joomla database and import the sites actual database, which was exported as a mysql dump. Easy right? All you need to do is drop the database and create a new database. But I did not have permission to do this. In fact, I had very little knowledge of the environment and how it was initially set up, I was called to help get the site back online again.

At this point it was obvious that I would need to use DROP TABLE tableName statements, but since joomla has quite a few tables and its extensions also have their own table. There were about 102 tables in total. Doing this manually seemed insane (we programmers do not like hard work :) ). So the first thought that jumped to mind was, I know, I will write a program to do this. But wait, what about input for that program? Well, there is a command to list all the tables in mysql SHOW TABLES which will list all the tables in the currently selected database. Great, so now all I have to do is to parse the data, maybe add it to a list, python's comma seperated list syntax should do. Finally, as I was manually going over the output I got from the show tables command, changing it to be an list in python, it hit me. Why do I need to do all this work to begin with? There is a much better way.

Solution

First lets list all the tables in the database again using ‘SHOW TABLES;’ in the mysql command line and look at the format of the returned result set.

Figure 1: output from running 'SHOW TABLES;' in MySQL CommandLine

As you can see from the picture above each line contains the table name and starts and ends with a | character. Beautiful, so why not just use a search and replace? There is even no need for regular expressions, so any editor will do.

1. Copy the output of the SHOW TABLES command (just the rows with the table names) to a text editor
2. Replace “ |” (space and vertical pipe characters) with “;” (a semi-colon)
3. Replace “|” (vertical pipe character) with “DROP TABLE”
4. Save the results into a text file called “drop_tables.sql”
5. Run the following command on your server:

> mysql –user=db_user –password=pass123 –database=db_name < drop_tables.sql

The last step would run the drop statements on your database. Note that you do not have to do this through the command line, you can just run it the same way you are used to running sql commands on your database (through phpMyAdmin for example).

After step 3 your file should look like this:

DROP TABLE jos_assets                 ;
DROP TABLE jos_banner_clients         ;
DROP TABLE jos_banner_tracks          ;
DROP TABLE jos_banners                ;
DROP TABLE jos_categories             ;
DROP TABLE jos_contact_details        ;
DROP TABLE jos_content                ;
DROP TABLE jos_content_frontpage      ;
DROP TABLE jos_content_rating         ;
DROP TABLE jos_core_log_searches      ;
DROP TABLE jos_extensions             ;
DROP TABLE jos_languages              ;
DROP TABLE jos_menu                   ;
DROP TABLE jos_menu_types             ;
DROP TABLE jos_messages               ;
DROP TABLE jos_messages_cfg           ;
DROP TABLE jos_modules                ;
DROP TABLE jos_modules_menu           ;
DROP TABLE jos_newsfeeds              ;
DROP TABLE jos_redirect_links         ;
DROP TABLE jos_schemas                ;
DROP TABLE jos_session                ;
DROP TABLE jos_template_styles        ;
DROP TABLE jos_update_categories      ;
DROP TABLE jos_update_sites           ;
DROP TABLE jos_update_sites_extensions;
DROP TABLE jos_updates                ;
DROP TABLE jos_user_profiles          ;
DROP TABLE jos_user_usergroup_map     ;
DROP TABLE jos_usergroups             ;
DROP TABLE jos_users                  ;
DROP TABLE jos_viewlevels             ;
DROP TABLE jos_weblinks               ;

Other Databases

For all other databases, the steps would be roughly the same. The main difference would be how you get all the tables in your database. You may also need to use regular expressions for the find and replace steps. Here is list all tables in several popular database systems:

Microsoft SQL:

SELECT * FROM sysobjects WHERE type = 'U';

IBM DB2:

LIST TABLES FOR ALL;

PostgreSQL:

SELECT * FROM information_schema.tables

Conclusion

What I learned from this was that sometimes things are as easy as they seem. We really ought to give things a second thought to come up with simpler solutions, complicated solutions are usually the first ones to come to mind. 

---

Add this page to your favorite Social Bookmarking websites