Delete all tables with the same prefex from a MySQL Database

There have been times when I need to drop many tables in a MySQL database. Usually the table names start with similar names. Of example if had a Moodle installation you may have 200 table names starting with moodle_ or if you have WordPress you may have 150 tables starting with wp_. Now the time has come you want to delete Moodle or WordPress and don’t need all those tables in your MySQL database anymore but deleting them one at a time is too time consuming.  Here is how I do it and it takes about 2 minutes.

If you are working with a remote database save yourself some time and download and install MySQL Workbench on your computer.

For my tutorial I will used this information as my database and table:
Example Database Name is:    MyDataBase
Example Table Name is:         TBL_data1

If you wanted to delete this table manually you would use this MySQL statement:
drop table `MyDataBase`.`TBL_data1`;

Now you could type that in for every table but after the 150th you might get tired of it. Follow these steps to delete them all at the same time:

** Step 1 **
-> login to your MySQL database. I used MSQL Workbench

** Step 2 **
-> use this  MySQL Statement: Show TABLES;
this will print out all the tabls in the database like so:
TBL_data1
TBL_data2
TBL_data3
TBL_data4
TBL_data5

** Step 3 **
-> now copy all the tables you wish to delete and past them into a text editor like notepad++
You will now have a list of tables like this in your text editor:
‘TBL_data1’
‘TBL_data2’
‘TBL_data3’
‘TBL_data4’
‘TBL_data5’

** Step 4 **
Add the beginning of the SQL statement
-> use find and replace: find all ‘TBL_ and replace with: drop table `MyDataBase`.`TBL_
This will add the beginning of the SQL statement to each table so it looks like this:
drop table `MyDataBase`.`TBL_data1′
drop table `MyDataBase`.`TBL_data2′
drop table `MyDataBase`.`TBL_data3′
drop table `MyDataBase`.`TBL_data4′
drop table `MyDataBase`.`TBL_data5′

** Step 5 **
Now finsih the SQL statement by adding the `:
-> use find and replace: Find all ‘ to a `;

** Step 6 **
Now copy and run all the statements you created.
-> Past all the SQL statements into MySQL (again I am using MySQL Workbench)
-> press ctrl+shift+enter to run all the statements
Done.

This entry was posted in Computers & Technology, Uncategorized. Bookmark the permalink.