There may come a time when you need to drop all tables in a MySQL database. In this article, you’ll learn how to do that easily.
Removing all of the tables in the database will remove all of the data in the tables, giving you what looks like an empty database.
You can drop the database, and recreate it, but you’ll lose the users, their permissions, and any other objects you have created such as views and stored procedures.
Dropping the tables is an easy thing to do if you want to reset your data.
Using SQL Query
Having some foreign key constraints might prevent you from executing drop table, so the first thing you should do is to temporarily disable all the foreign key constraints in order for the drop statements work:
SET FOREIGN_KEY_CHECKS = 0;
Then you list all the available tables from the current database:
SELECT
table_name
FROM
information_schema.tables
WHERE
table_schema = db_name;
And delete all tables on by one from the list:
DROP TABLE IF EXISTS table1;
Remember to turn on foreign key constraint after it’s done:
SET FOREIGN_KEY_CHECKS = 1;
Using mysqldump
There’s another workaround with mysqldump which is faster and easier.
First, disable foreign key check:
echo "SET FOREIGN_KEY_CHECKS = 0;" > ./temp.sql
Then dump the db with no data and drop all tables:
mysqldump --add-drop-table --no-data -u root -p db_name | grep 'DROP TABLE' >> ./temp.sql
Turn the foreign key check back on:
echo "SET FOREIGN_KEY_CHECKS = 1;" >> ./temp.sql
Now restore the db with the dump file:
mysql -u root -p db_name < ./temp.sql