MySQL Procedure: Drop All Tables in Database

If you ever need a progmatic way to drop all tables in a database, you can use the following stored procedure:

~

If you ever need a progmatic way to drop all tables in a database, you can use the following stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `drop_all_tables_from` $$

CREATE PROCEDURE `drop_all_tables_from`(IN schema_target VARCHAR(128))
BEGIN
    DECLARE table_list TEXT;

    SELECT
        GROUP_CONCAT(`TABLE_NAME`)
    INTO
        table_list

    FROM `information_schema`.`TABLES`
    WHERE
          `TABLE_SCHEMA` = schema_target;

    IF table_list IS NOT NULL THEN
        SET @drop_tables = CONCAT("DROP TABLE ", table_list);

        PREPARE stmt FROM @drop_tables;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

END $$

DELIMITER ;

To use, simply call the procedure with the database name as the parameter:

CALL drop_all_tables_from("database_name"); 

Note: This will drop all tables, without confirmation for the database name provided.


~

Comments