Drop Multiple Tables in MySQL

Dropping a database table is a common occurrence for any database administrator. Whether you are just cleaning up unused data or optimizing performance, you will eventually encounter a scenario where you need to drop a single or multiple database tables.

In this tutorial, we will explore various methods you can use to drop multiple tables in the MySQL database

NOTE: This is a very DESCTRUCTIVE action. All the commands and scripts provided in this tutorial will permanently delete data stored at any specified table.

Requirements

To following along with this post, ensure you have the following

• Basic knowledge of SQL
• MySQL Server Installed (Version 8.0 recommended)

MySQL Sequential Drop

The most basic way of dropping multiple tables in MySQL is using multiple DROP TABLE statements for each table you wish to delete.

For example, if you wish to remove table1, table2, table3, you can run multiple queries as shown

DROP TABLE table1;
DROP TABLE table2;
DROP TABLE table3;

In this case, we use a set of sequential DROP statements to remove the tables.

MySQL Drop Table Command - One Liner

MySQL also provides us with the ability to specify more than one table in the DROP TABLE command. This allows us to quickly delete multiple tables in a single command.

The command syntax is as shown:

DROP TABLE table1, table2, table3;

MySQL Pattern Based Drop

We can also take advantage of MySQL scripting feature to create a simple statement that drops multiple tables that match a specific criteria.

For example, suppose we wish to drop multiple tables that start with the name default

SELECT Concat('DROP TABLE ', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema = 'database_name' 
AND table_name LIKE 'default%';

Ensure to replace 'database_name' with the name of your target database. This will generate a list of DROP TABLE commands. You can then execute these commands to drop the tables.

You can also combine the resulting statements above to quickly and automatically execute the generated statements using the PREPARE and EXECUTE commands:

SET @tables = NULL;
SELECT GROUP_CONCAT(table_name SEPARATOR ', ') INTO @tables 
FROM information_schema.tables 
WHERE table_schema = 'database_name' AND table_name LIKE 'default%';

SET @dropTableStatement = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @dropTableStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The script above performs the following actions:

1. We use the GROUP_CONCAT function to concatenate the names of all tables that start with default into a single string, separated by commas.
2. Next, we create a DROP TABLE statement using this string.
3. Finally, we call the PREPARE and EXECUTE to execute this statement.

End.

In this tutorial, we explored three main methods you can use to drop multiple tables in MySQL. Like any other programming language, you can take advantage of the provided MySQL features to come up with alternative ways of accomplishing the same thing.