In this article we will look at a way to delete multiple database tables quickly without using the DROP DATABASE dbName statement. This can be very useful if you do not have the permissions to DROP the database, but need to reset all the tables in it.

Problem

As part of changing one of our sites over to a new server I had to delete the existing Joomla database and import the updated database, which was exported earlier using mysqldump. 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 running on a new server.

Next I checked if it was possible to use DROP TABLE with the same account, luckily it was. But since joomla tries to do everything under the sun, it has quite a few tables and each extension also has separate table. There were about 102 tables in total. Doing this manually seemed insane (we programmers do not like hard work :) ). The first thought that jumped to mind was: I know, I will write a program to do this. But wait, what would be the input for this new script? 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, and 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 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 pipeline (|) character. Then why not just use a search and replace? There is 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 will actually execute the drop table statements on your database. Note that you do not have to do this through the command line, you can just run it using phpMyAdmin instead.

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 is how listing all tables is done. You may also need to use regular expressions for the find and replace steps. Here is 'list all tables' command 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.