How to Drop All Tables in MySQL
This tutorial demonstrates several ways a user can drop all tables in MySQL and lists sample scripts to make it possible.
The reason why you can’t outright drop all tables in a single line of code is that in a sizeable and well-designed database, there will likely be a lot of foreign key constraints.
SQL Query to Drop All Tables
-
The first thing to do is to make it so that dropping tables do not need to check foreign key constraints.
SET FOREIGN_KEY_CHECKS = 0; -
Then proceed to write a script to query all tables within your database.
SELECT table_name FROM information_schema.tables WHERE table_schema = db_name; -
Afterwards, copy all of the tables in the result from the above query and delete them one by one.
DROP TABLE IF EXISTS tableOne; DROP TABLE IF EXISTS tableTwo; DROP TABLE IF EXISTS tableThree; DROP TABLE IF EXISTS tableEtc; -
Reset the configuration of foreign key checking to default
SET FOREIGN_KEY_CHECKS = 1;
This approach is possible because we disabled foreign key checking at the start. So whatever order we delete the tables in, it won’t throw any constraint error. Just make sure to re-enable foreign key checking after the script.
Empty and Recreate the Database
For this to be possible, you need to make sure that other than tables, you also are willing to delete stored functions, indices, roles, and other settings apart from tables since you are dropping the whole database.
For this to be executed successfully, the current role must have drop and create privileges in the database.
DROP DATABASE database_name;
After dropping, recreate the database.
CREATE DATABASE database_name;
This approach is most likely the most volatile method of dropping all tables. This is suitable only for small or experimental databases.
Use mysqldump to Drop Tables in MySQL
mysqldump is a console command attributed from MySQL. Assuming you already have MySQL set up in your path variables, we can use the command to drop tables within your database.
-
The first step still is to disable foreign key checking in the console.
echo "SET FOREIGN_KEY_CHECKS = 0" > ./temp.sql -
Proceed to dump the database using
mysqldumpand drop the tablesmysqldump --add-drop-table --no-data -u rootuser -p database_name | grep 'DROP TABLE' >> ./temp.sql -
Re-enable foreign key checking in the console.
echo "SET FOREIGN_KEY_CHECKS = 1;" >> ./temp.sql -
Restore your database using the dump file
mysql -u root -p db_name < ./temp.sql
This will work easier if you have knowledge over console commands and have MySQL set up in your path variable.
Verification of Deleted Tables
To verify whether all tables actually were deleted, we can check the database if it still outputs the tables with this script.
SHOW TABLES FROM database_name;
If there is no output, that means that the query has been executed successfully, and all SQL tables from that specific database have been dropped.
In summary, the reason why you can’t delete tables in bulk directly in SQL is that relational databases rely heavily on relationships and constraints. If you were allowed to delete tables without explicitly disabling foreign key checking, the relationships, constraints, and foreign key configurations will be corrupted.
Skilled in Python, Java, Spring Boot, AngularJS, and Agile Methodologies. Strong engineering professional with a passion for development and always seeking opportunities for personal and career growth. A Technical Writer writing about comprehensive how-to articles, environment set-ups, and technical walkthroughs. Specializes in writing Python, Java, Spring, and SQL articles.
LinkedIn