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.
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:
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.