MySQL - Deleting Some Specific Tables From Big Database

How can we delete some specific tables from a database. For instance I have a database with more开发者_运维问答 than 20.000 tables and I need to delete the one which contains some specific strings in their names. So how can I do it? Is there a way to get the all table names from the database?

You can get tables with certain names from information_schema.

This is how you get a list of the tables in your database:

select table_name from information_schema.tables;

With that in mind, you can generate a script to drop the tables you need:

select concat('drop table ', table_name, ';')
  from information_schema.tables;

Then copy that script and paste it on a SQL interpreter.

You could also filter tables based on their names or databases:

select concat('drop table ', table_name, ';')
  from information_schema.tables
 where table_name like 'abc%'
   and table_schema = 'myDatabase'; --db name

agree w/@n8wrl, but if you must you could use an IF ... THEN statement http://dev.mysql.com/doc/refman/5.0/en/if-statement.html to DROP TABLE

The information_schema views are helpful for listing and filtering tables in any ANSI compliant database:

select *
  from information_schema.tables T
 where T.table_name like '%FILTER HERE%'

You can loop through and drop the relevant tables using dynamic SQL and a cursor through the above recordset.