Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?
I found the generated set of drop statements useful, and recommend these tweaks:
SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';
Note 1: This does not execute the DROP statements, it just gives you a list of them. You will need to cut and paste the output into your SQL engine to execute them.
Note 2: If you have VIEWs, you'll have to correct each DROP TABLE `VIEW_NAME`
statement to DROP VIEW `VIEW_NAME`
manually.
"RESTRICT and CASCADE are permitted to make porting easier. In MySQL 5.5, they do nothing."
Therefore, in order for the drop statements to work if you need:
SET FOREIGN_KEY_CHECKS = 0
This will disable referential integrity checks - so when you are done performing the drops you need, you will want to reset key checking with
SET FOREIGN_KEY_CHECKS = 1
SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;
NB: to use output of SELECT easier, mysql -B option can help.