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.
