MySQL Drop All Tables At a Glance - For Speed Up Development

There may come a time when you need to drop all of your tables in a MySQL database but don't want to delete database. Consider you have 100+ tables in your database. So it will be big problem for all of us to delete all tables in short time. If you have foreign keys in your database, then you may encounter errors if you drop a table that is related to another table using foreign keys. The quicker way to do this is to disable the foreign key checks when these statements are run, so you can drop the tables and avoid the error. Add this line above all of your Drop Table statements to disable the foreign key checks: SET FOREIGN_KEY_CHECKS = 0; Then, add this line at the end of your script to enable them: SET FOREIGN_KEY_CHECKS = 1;

So what we will do is Drop All Tables In One Script to speed up our whole process.

SET FOREIGN_KEY_CHECKS = 0; SET GROUP_CONCAT_MAX_LEN=32768; SET @tables = NULL; SELECT GROUP_CONCAT('`', table_name, '`') INTO @tables FROM information_schema.tables WHERE table_schema = (SELECT DATABASE()); SELECT IFNULL(@tables,'dummy') INTO @tables; SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables); PREPARE stmt FROM @tables; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1;