Select statement on tables with the same name and schema but in different databases

Question

I have a table that lists the names of the databases that have tables with the same schema. Each customer has a different database.

Is there a way to run a select on same table in multiple databases?

The first query I wrote was to get a list of databases concatenated the table name.

Select concat(database_name,'.','TableA') from dbnamelist

This results in output like

database1.TableA
database2.TableA

Now I want to select a value in the column type but across different tables.

Select type from database1.TableA,database2,TableA

Is it possible to do this in a single SQL statement?

• Akina: Is it possible to do this in a single SQL statement? No. Create stored procedure. Use prepared statement.
• user9445: Thank you! Prepared statement is a good idea.

Answer 1

SELECT GROUP_CONCAT(CONCAT(table_schema, '.', tableA))
 FROM information_schema.tables
 WHERE table_name = 'tableA';

will generate the list you need.

Figure out what the query needs to look like (and I do not think your example is correct), and modify this SELECT to generate the desired query.

Then copy and paste it into mysql commandline tool to run it.