Software creative always looking for a challenge

Deleting Multiple Database Tables – The Easy Way

SQL Code

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:
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:

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:

IBM DB2:

PostgreSQL:

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.

Be Sociable, Share!