How to drop all tables in MySQL?

1. 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;

DROP TABLE IF EXISTS table2;

DROP TABLE IF EXISTS table3;

Remember to turn on foreign key constraint after it’s done:

SET FOREIGN_KEY_CHECKS = 1;

2. 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

3. Using TablePlus GUI Tool:

Connect to the target database
Select all tables from the left sidebar
Right-click and choose delete, or simply hit delete button
Press Cmd + S to commit changes to the server

Drop all tables in TablePlus GUI Tool MySQL

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 