MySQL 8.0 - Drop all tables without dropping the database

sachin

Writing a PROCEDURE will be a 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 ;