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!