MySQL : Drop/ Truncate all the tables in a schema - DevDummy

Latest

Views | Thoughts | Concepts | Techniques

Sunday, October 28, 2018

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;

...

1 comment:

  1. Very rapidly this website will be famous amid all blogging visitors, due to it's nice articles

    ReplyDelete