From time to time I stumbled over the case, that I had to drop all tables from a MySQL database. In phpMyAdmin or other GUI-based tools – in general – you only have the possibility to drop the whole database. But then you have to recreate it from the scratch. Or you have to click-drop each table separately. Another common – but not that clever – practise is it to delete each table by a separate drop command or by list them manually in a drop statement (see https://dev.mysql.com/doc/refman/5.7/en/drop-table.html). All at all not that comfortable!
That’s why I wrote this MySQL database procedure to drop all tables from MySQL databases.
# DROP all Tables DELIMITER //; DROP PROCEDURE IF EXISTS kill_all_tables; CREATE PROCEDURE kill_all_tables(IN dbname VARCHAR(255), OUT tables TEXT) BEGIN # disable foreign key checks to prevent errors SET FOREIGN_KEY_CHECKS = 0; SET SESSION group_concat_max_len = 20000; # Select all tables from given Database SELECT GROUP_CONCAT('`', table_schema, '`.', table_name) INTO @tablesToDrop FROM information_schema.tables WHERE table_schema = dbname; SELECT @tablesToDrop; # Append determined table names to DROP TABLE statement SET @dropcommad = NULL; SET @dropcommad = CONCAT('DROP TABLE ', @tablesToDrop); SELECT @dropcommad; # create a mysql command From PREPARE stmt FROM @dropcommad; EXECUTE stmt; DEALLOCATE PREPARE stmt; # delete it afterwards # reenable foreign key checks again SET FOREIGN_KEY_CHECKS = 1; END; DELIMITER ; CALL kill_all_tables('dbname', @erg); SELECT @erg; DELIMITER ;
The inspiration for this procedure I got from http://stackoverflow.com/questions/12403662/how-to-drop-all-mysql-tables-from-the-command-line (first answer by Devart). Thanks a lot!