Drop All Tables in MySQL

As a database administrator, you will come across instances where you need to drop all the tables in a given MySQL database. Whether you are cleaning up, schema changes, data migration, testing, troubleshooting, etc, learning varios method and techniques of dropping all tables is critical.

## Using a Single DROP TABLE Command

The first method you can use is the typical DROP TABLE command in MySQL. This is applicable when you have a few tables and you are already familiar with their names.

The command syntax is as shown

DROP TABLE table1, table2, table3;

This should remove the tables specified in the statement. Ensure you provide the correct table names as misspelling the table name can lead to data loss.

Using INFORMATION_SCHEMA

However, if you have a lot of table you wish to remove, the DROP TABLE command is not going to cut it. Instead, you can use the INFORMATION_SCHEMA table to generate a script that allows you to quickly and efficiently remove the tables specified in that database.

SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'database_name';

NOTE: The command above does not actually drop the tables. Instead, it just prints out the statements you would need to drop the actual tables. Simply copy the resulting statements and execute them on the target machine.

Using MySQL Shell Script

And we cannot forget native Unix scripting. We can use this feature to automate a simple script that connects to the MySQL server and run the commands to remove the tables.

mysql --user=username --password=password your_database -sN -e "SHOW TABLES" | while read table; do mysql --user=username --password=password -e "DROP TABLE $table" database_name; done

The script above connects to the database, gets a list of all table names, and drops them one by one.

Using MySQL Stored Procedure

And finally, we have the concept of MySQL stored procedures. Although MySQL does not support batch delete natively, you can create a stored procedure that does this as demonstrated in the provided script below:

DELIMITER $$

CREATE PROCEDURE DropAllTables()
BEGIN
    DECLARE @table_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name 
        FROM information_schema.tables
        WHERE table_schema = 'database_name';
        
    OPEN cur;
    
    REPEAT 
        FETCH NEXT FROM cur INTO @table_name;
        SET @sql = CONCAT('DROP TABLE ', @table_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    UNTIL done END REPEAT;
    
    CLOSE cur;
END$$

DELIMITER ;

Once defined, you can simply call the procedure as:

CALL DropAllTables();

Conclusion

This post covered some basic methods of dropping all the tables in a given MySQL database. Use the methods discussed above in a cautious manner as it will lead to data loss.