Drop all databases that have under certain number of tables

Question

I have a MYSQL server with lots of inactive databases. All this "dead" DBs have under 109 tables each, so I'm looking for a way to drop all of them at once, but haven't found a way to make this. The other way I'm thinking I could delete all of them is by dropping all the databases that don't have X table (where X is the latest table added to our model).

Answer 1

You can identify the databases with the following query:

select schema_name
from information_schema.tables
group by schema_name
having count(*) < 109

You can then use the list of schema_name to create "drop database" commands in a tool such as Excel. Or, put in a cursor to cycle through them. Or read them into a scripting language of your preference to do the drop.