MySQL DROP all tables, ignoring foreign keys

Question

Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?

Answer 1

I found the generated set of drop statements useful, and recommend these tweaks:

1. Limit the generated drops to your database like this:

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.

2. Note, per http://dev.mysql.com/doc/refman/5.5/en/drop-table.html, dropping with cascade is pointless / misleading:

"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 

3. The final execution should look like:

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.

Answer 2

From http://www.devdaily.com/blog/post/mysql/drop-mysql-tables-in-any-order-foreign-keys:

SET FOREIGN_KEY_CHECKS = 0; drop table if exists customers; drop table if exists orders; drop table if exists order_details; SET FOREIGN_KEY_CHECKS = 1; 

(Note that this answers how to disable foreign key checks in order to be able to drop the tables in arbitrary order. It does not answer how to automatically generate drop-table statements for all existing tables and execute them in a single script. Jean's answer does.)