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.
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>;
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;
...
Very rapidly this website will be famous amid all blogging visitors, due to it's nice articles
ReplyDelete