MySQL : Drop all the tables in a schema - DevDummy

Latest

Guildes | Concepts | Techniques

Sunday, October 28, 2018

MySQL : Drop all the tables in a schema


Drop 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 Each table

Tables can be dropped selectively,

DROP TABLE IF EXISTS <table>;

4. Enable foreign key check options

Make sure you enable the foreign key check back to make sure the integrity of your database.

SET FOREIGN_KEY_CHECKS = 1;

...

No comments:

Post a Comment