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


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?


1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.