MySQL ・プロシージャでテーブルを一括削除

• システム上に永続配置は危険です。

sql

DROP PROCEDURE IF EXISTS dropTables;
DELIMITER ;;
CREATE PROCEDURE dropTables()
BEGIN
    DECLARE T VARCHAR(255);
    DECLARE M BOOLEAN;
    DECLARE D INT DEFAULT 0;
    DECLARE C CURSOR FOR SELECT TABLE_NAME FROM `information_schema`.tables WHERE table_schema = 'dbname' AND table_type = 'BASE TABLE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET D=1;

    SET FOREIGN_KEY_CHECKS = 0;
    OPEN C;
    L: LOOP
        FETCH C INTO T;
        IF D THEN
            LEAVE L;
        END IF;

        SET @drop_sql_ref = CONCAT('DROP TABLE IF EXISTS  ' , T );
        PREPARE drop_sql FROM @drop_sql_ref;
        EXECUTE drop_sql ;
    END LOOP;
    CLOSE C;
    SET FOREIGN_KEY_CHECKS = 1;
END;
;;
DELIMITER ;

SHOW TABLES;
CALL dropTables();
SHOW TABLES;

DROP PROCEDURE IF EXISTS dropTables;