1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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`; |
Source: https://stackoverflow.com/questions/3476765/mysql-drop-all-tables-ignoring-foreign-keys
