How to Drop All Tables in MySQL

Rayven Esplanada Feb 02, 2024 MySQL MySQL Table

SQL Query to Drop All Tables
Empty and Recreate the Database
Verification of Deleted Tables

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.
SQL
```
 sqlCopySET FOREIGN_KEY_CHECKS = 0;
```

Then proceed to write a script to query all tables within your database.

SQL

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

SQL

 sqlCopyDROP 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
SQL
```
 sqlCopySET 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.

SQL

 sqlCopyDROP DATABASE database_name;

After dropping, recreate the database.

SQL

 sqlCopyCREATE 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.
Bash
```
 bashCopyecho "SET FOREIGN_KEY_CHECKS = 0" > ./temp.sql
```

Proceed to dump the database using `mysqldump` and drop the tables

Bash

 bashCopymysqldump --add-drop-table --no-data -u rootuser -p database_name | grep 'DROP TABLE' >> ./temp.sql

Re-enable foreign key checking in the console.

Bash

 bashCopyecho "SET FOREIGN_KEY_CHECKS = 1;" >> ./temp.sql

Restore your database using the dump file

Bash

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

SQL

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

Enjoying our tutorials? Subscribe to DelftStack on YouTube to support us in creating more high-quality video guides. Subscribe

Author: Rayven Esplanada

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.