Generally, web developers and database administrators need to drop one or more databases entirely. But sometimes, you may need to remove all tables in MySQL. In this article, we have learnt how to remove all tables in MySQL without removing database.
How to Remove All Tables in MySQL
There are several ways to remove all tables in MySQL without removing the database.
One of the simplest ways to do this is to use MySQLdump to export only the database structure and not the underlying data using –no-data option. This will produce a sequence of SQL queries to export the database along with its tables, constraints but without any data. We then use grep to search for commands beginning with DROP, FOREIGN KEY CHECK, and USE command to get a list of drop commands used in above output, as well as disable/enable foreign key checks at appropriate places. Finally, we pass these commands to mysql command to execute them, thereby dropping the tables.
Here is the command to do so. Replace [USERNAME], [PASSWORD], [DATABASE] with the username, password and database respectively.
mysqldump -u[USERNAME] -p[PASSWORD] \ --add-drop-table --no-data [DATABASE] | \ grep -e '^DROP \| FOREIGN_KEY_CHECKS' | \ mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
If you have the privileges to drop & create the database you can do so with the following commands. But please note, the following command will also delete any setting related to old database and create a new fresh database.
mysql> drop database [database name]; mysql> create database [database name];
Alternatively, you can use the following set of prepared statements to create and run DROP TABLE command using a list of table names in database. Replace database_name with the name of database.
SET FOREIGN_KEY_CHECKS = 0;
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables
FROM information_schema.tables
WHERE table_schema = 'database_name';
SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;
In the above statements, first we disable foreign key checks to avoid any error while deleting tables with foreign key constraints. Then we select a list of table names pertaining to database from information_schema.tables which stores all table names of all databases in your MySQL server. We use group_concat() function to concatenate the list of table names into a string of comma-separated table names.
Then we use concat() function to prefix DROP TABLE command to above string to create our SQL statement. Then we use EXECUTE command to run this statement. Lastly, we re-enable foreign key checks.
In this article, we have learnt several ways to remove all tables in MySQL databases.
Also read:
How to Skip Tables in MySQLdump
How to Reset MySQL Query Cache Without Restarting
How to Delete All Rows in MySQL table Except Some
How to Fix Unknown Column in Field List in MySQL
How to Loop Through All Rows of Table in MySQL