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 PROCEDURE IF EXISTS kill_all_tables;
CREATE PROCEDURE kill_all_tables(IN dbname VARCHAR(255), OUT tables TEXT)
# 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)
WHERE table_schema = 'dbname';
# Append determined table names to DROP TABLE statement
SET @dropcommad = NULL;
SET @dropcommad = CONCAT('DROP TABLE ', @tablesToDrop);
# create a mysql command From
PREPARE stmt FROM @dropcommad;
DEALLOCATE PREPARE stmt; # delete it afterwards
# reenable foreign key checks again
SET FOREIGN_KEY_CHECKS = 1;
CALL kill_all_tables('dbname', @erg);
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!