MySQL DROP all tables, ignoring foreign keys

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.tablesWHERE 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 hereSET FOREIGN_KEY_CHECKS = 1;

NB: to use output of SELECT easier, mysql -B option can help.

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.)

Here is SurlyDre's stored procedure modified so that foreign keys are ignored:

DROP PROCEDURE IF EXISTS `drop_all_tables`;DELIMITER $$CREATE PROCEDURE `drop_all_tables`()BEGIN    DECLARE _done INT DEFAULT FALSE;    DECLARE _tableName VARCHAR(255);    DECLARE _cursor CURSOR FOR        SELECT table_name         FROM information_schema.TABLES        WHERE table_schema = SCHEMA();    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;    SET FOREIGN_KEY_CHECKS = 0;    OPEN _cursor;    REPEAT FETCH _cursor INTO _tableName;    IF NOT _done THEN        SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);        PREPARE stmt1 FROM @stmt_sql;        EXECUTE stmt1;        DEALLOCATE PREPARE stmt1;    END IF;    UNTIL _done END REPEAT;    CLOSE _cursor;    SET FOREIGN_KEY_CHECKS = 1;END$$DELIMITER ;call drop_all_tables(); DROP PROCEDURE IF EXISTS `drop_all_tables`;