mysql - Query a database with results from multiple tables?

Question

There are some similar questions around but they aren't quite what I'm looking for, so forgive me if you think this is answered elsewhere.

I am basically looking for an easy way to do things as I have over 4000 tables to get data from. This kind of follows on from my previous post: mysql search for segment of table name

The general situation is that I have a database filled with tables and I only want about a quarter of this which comes to around 4000 tables. I have a list of the individual table names thanks to my previous post, but I want the data that goes with them. I know that for an individual one I can do SELECT table1.*, table2.*; or something similar but I don't want to go through all 4000 or so. They all end with the same thing, e.g. staff_name, manager_name, customer_name so I can use

SHOW TABLES LIKE '%_name'

to see the table names that I want in the database. Someone suggested using dynamic mysql, but I don't even know where to start with that. Any suggestions?

Answer 1

I would suggest generating the SQL statement.

Try doing:

select concat('select * from ', table_name) as query
from Information_Schema.tables
where table_schema = <dbname> and
      table_name like <whatever>

You can then run this as a bunch of queries by copying into a query editor window.

If you want everything as one query, then do:

select concat('select * from ', table_name, ' union all ') as query
from Information_Schema.tables
where table_schema = <dbname> and
      table_name like <whatever>

And remove the final "union all".

This has the table name matching a like. Leave out the table_name part of the WHERE to get all tables. Or, include specific tables using table_name in ().