html

When working with MySQL databases during development or testing, you might encounter situations where you need to completely reset a database by removing all tables while preserving the database itself. This is particularly common in:

• Test environment cleanup between test runs
• Resetting demo databases
• Preparing for database migrations

Simply dropping and recreating the database isn't always an option because:

1. You might lack permissions to create new databases
2. Other database objects (views, procedures, functions) need to be preserved
3. Database-level configurations should remain intact

The most efficient approach uses MySQL's information_schema to generate and execute DROP statements dynamically:

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SET @tables = NULL;
SELECT GROUP_CONCAT('', table_name, '') INTO @tables
  FROM information_schema.tables
  WHERE table_schema = (SELECT DATABASE());
SELECT IFNULL(@tables,'dummy') INTO @tables;

SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

The solution includes FOREIGN_KEY_CHECKS to temporarily disable constraint checking. This is crucial because:

• Tables might have circular dependencies
• Dropping order matters with foreign keys
• Disabling checks prevents errors during mass deletion

For frequent use, create a reusable stored procedure:

DELIMITER //
CREATE PROCEDURE drop_all_tables()
BEGIN
  DECLARE _done INT DEFAULT FALSE;
  DECLARE _tableName VARCHAR(255);
  DECLARE _cursor CURSOR FOR
    SELECT table_name 
    FROM information_schema.tables
    WHERE table_schema = DATABASE();
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
  
  SET FOREIGN_KEY_CHECKS = 0;
  OPEN _cursor;
  
  read_loop: LOOP
    FETCH _cursor INTO _tableName;
    IF _done THEN
      LEAVE read_loop;
    END IF;
    SET @s = CONCAT('DROP TABLE IF EXISTS ', _tableName, '');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  
  CLOSE _cursor;
  SET FOREIGN_KEY_CHECKS = 1;
END //
DELIMITER ;

For those working directly with MySQL command line:

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done

When working with MySQL database migrations or test environment cleanup, developers often need to wipe all tables while preserving the database structure itself. The naive approach of dropping the entire database isn't viable when you lack recreation privileges or need to maintain database-level permissions and configurations.

The most reliable method involves querying the information_schema to dynamically generate DROP TABLE statements:

SET FOREIGN_KEY_CHECKS = 0;
SET GROUP_CONCAT_MAX_LEN=32768;
SELECT CONCAT('DROP TABLE IF EXISTS ', GROUP_CONCAT(table_name), ';')
INTO @drop_tables
FROM information_schema.tables
WHERE table_schema = (SELECT DATABASE());
PREPARE stmt FROM @drop_tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

For frequent use, create a reusable stored procedure:

DELIMITER //
CREATE PROCEDURE drop_all_tables()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _table_name VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.tables
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
    
    SET FOREIGN_KEY_CHECKS = 0;
    OPEN _cursor;
    
    read_loop: LOOP
        FETCH _cursor INTO _table_name;
        IF _done THEN
            LEAVE read_loop;
        END IF;
        SET @s = CONCAT('DROP TABLE IF EXISTS ', _table_name, '');
        PREPARE stmt FROM @s;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    
    CLOSE _cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END //
DELIMITER ;

For shell scripting scenarios, use mysqlshow and awk:

mysql -uusername -ppassword -Nse 'show tables' databasename | while read table; do mysql -uusername -ppassword -e "drop table $table" databasename; done

The solutions above include FOREIGN_KEY_CHECKS to prevent constraint errors. For production environments, consider:

-- First disable all constraints
SELECT CONCAT('ALTER TABLE ', table_name, ' DISABLE KEYS;') 
FROM information_schema.tables 
WHERE table_schema = DATABASE();

-- Then generate drop statements
-- Finally re-enable constraints for remaining tables

For databases with hundreds of tables, batch processing may be necessary:

SELECT CONCAT('DROP TABLE IF EXISTS ', GROUP_CONCAT(table_name SEPARATOR ', '), ';')
FROM (
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = DATABASE()
    LIMIT 50
) AS batch;