MySQL : Drop/ Truncate all the tables in a schema

Drop/ Truncate all the tables in a schema in MySQL Database

It is often required to delete tables from a MySQL schema. Following is a simple SQL guide that can be used for deleting the all or the specified tables.

1. Turn off the foreign key checks

First we need to turn off the foreign key check within the tables. Otherwise, an error would be thrown due to the key constraints.

SET FOREIGN_KEY_CHECKS = 0;

2. Collect all table names

If the table names are not known, you can query the information_schema table to find them.

SELECT

    table_name

FROM

    information_schema.tables

WHERE

    table_schema = db_name;

3. Drop/ Truncate each table

Tables can be dropped selectively,

DROP TABLE IF EXISTS <table1>;
DROP TABLE IF EXISTS <table2>;
DROP TABLE IF EXISTS <table3>;

4. Enable foreign key check options

Enable the foreign key checks to make sure the integrity of your database, if required.

SET FOREIGN_KEY_CHECKS = 1;

...