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
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.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.