Share

Tweet

Have you ever wanted to drop all MySQL tables leaving the database untouched? Forget about issuing a DROP TABLE LIKE '%' ... DROP ALL TABLE... etc. those commands don't exist.

Now is when a combination of the GROUP_CONCAT function and the INFORMATION_SCHEMA database become handy.

This simple SQL returns the exact line you have to run to drop all the tables at once:

Surround the statement with SET foreign_key_checks = X; to avoid complaints about foreign keys whatsoever.

SET foreign_key_checks = FALSE;

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT( table_name SEPARATOR ', ' ), ';' ) FROM information_schema.tables WHERE table_schema = 'ingmxdb';

SET foreign_key_checks = TRUE;