MySQL 8.0 - Drop all tables without dropping the database

sachin

Writing a PROCEDURE Nice and easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there

DELIMITER //


DROP PROCEDURE IF EXISTS DropAllTables //
CREATE PROCEDURE DropAllTables()
BEGIN
    -- Declare a variable to hold the dynamic SQL
    DECLARE done INT DEFAULT 0;
    DECLARE db_table_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = DATABASE();  -- Gets tables in the current database
    
    -- Handler to detect the end of the cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        
    -- Disable foreign key checks temporarily
    SET foreign_key_checks = 0;


    -- Open the cursor
    OPEN cur;


    -- Loop through each table and drop it
    read_loop: LOOP
        FETCH cur INTO db_table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;


        -- Drop the table dynamically
        SET @drop_sql = CONCAT('DROP TABLE IF EXISTS ', db_table_name);
        PREPARE stmt FROM @drop_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;


    -- Close the cursor
    CLOSE cur;


    -- Re-enable foreign key checks
    SET foreign_key_checks = 1;
END //

DELIMITER ;