How to drop multiple tables in mysql at once

Posted by

There are a couple of different ways to remove multiple tables from MySQL at one time. Almost all of the ways of dropping many mysql tables at once are dangerous and that’s probably why the MySQL developers have overlooked this “feature.” You run the risk of dropping tables outside of the ones you meant to drop.

But if you’re dead set on this, here’s a stored procedure for dropping multiple MySQL tables at one time that does the job quick and easy.

First you have to add the procedure to the schema. You can technically add this to any schema that you have access to. The way I use it is from a “global” schema that needs to make changes to other schemas that all have similar names. The MySQL user calling the procedure needs to be able to perform the DROP as well as READ the information schema tables. Don’t mess with your permissions unless you have to however.

Calling the procedure can be done with:

So in this case, I’m dropping all the tables that start with “jjj” in the “test” schema. The “pattern” parameter can be any valid MySQL “LIKE” parameter. In a MySQL client, it looks like this:

Now, obviously, if you put in a pattern that returns no resulting table names, then you’ll get an error. But fixing that error is left to the reader as an exercise ( do feel free to contribute that back to me!)

Any questions or comments? Do you have a better way to do this?

Leave a Reply

Your email address will not be published. Required fields are marked *