Say, we have a database and we are required to delete all the tables in it, then how will you proceed?

The first thing that clicks to mind is drop the database and recreate it.

mysql> drop database DB_NAME;
mysql> create database DB_NAME;

Is the issue here? Yes.

By dropping the database, all the permissions associated with the database will be lost and you will have to grant them again when you create the database.

Also, not only the tables but also triggers and stored procedures created in the database will be lost.

Say, now that you don’t have the permission to create or drop the database, then what do you do?

We can login to database and start deleting tables one by one.

mysql> drop table TABLE_NAME;

If you have foreign key checks applied then you will have to first disable the foreign key checks else you will get foreign key constraint error.

This solution will not work if you have a large number of tables.

Let us now show you how you can delete all the table in MySQL database in one step. We show you two options, you can choose to use any of these.

Option 1.

$ mysqldump -hHOSTNAME -uUSERNAME -pPASSWORD --add-drop-table --no-data DB_NAME | grep -e '^DROP \| FOREIGN_KEY_CHECKS' | mysql -hHOSTNAME -uUSERNAME -pPASSWORD DB_NAME;

Example:

$ mysqldump -hlocalhost -uroot -p'sw$HDK' --add-drop-table --no-data testdb | grep -e '^DROP \| FOREIGN_KEY_CHECKS' | mysql -hlocalhost -uroot -p'sw$HDK' testdb;

Option 2.

$ { mysql -hHOSTNAME -uUSERNAME -pPASSWORD -Nse 'show tables' DB_NAME; } | ( while read table; do if [ -z ${i+x} ]; then echo 'SET FOREIGN_KEY_CHECKS = 0;'; fi; i=1; echo "drop table \`$table\`;"; done; echo 'SET FOREIGN_KEY_CHECKS = 1;' ) | awk '{print}' ORS=' ' | mysql -hHOSTNAME -uUSERNAME -pPASSWORD DB_NAME;

Example:

$ { mysql -hlocalhost -uroot -p'sw$HDK' -Nse 'show tables' testdb; } | ( while read table; do if [ -z ${i+x} ]; then echo 'SET FOREIGN_KEY_CHECKS = 0;'; fi; i=1; echo "drop table \`$table\`;"; done; echo 'SET FOREIGN_KEY_CHECKS = 1;' ) | awk '{print}' ORS=' ' | mysql -hlocalhost -uroot -p'sw$HDK' testdb;

Once I faced a situation wherein I had no option to delete the database and was limited to use only the MySQL command line. I wasted a lot of time find and trying different ways to delete all tables in the database. So, I thought it will be worth sharing things that ultimately worked for me.