Drop All Tables in MySQL

  1. SQL Query to Drop All Tables
  2. Empty and Recreate the Database
  3. 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.
    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 mysqldump and drop the tables
    mysqldump --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.

Contribute
DelftStack is a collective effort contributed by software geeks like you. If you like the article and would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - MySQL Table

  • SELECT From Multiple Tables in MySQL