How to query against multiple databases on the same server

Question

I am not sure if this has been answered before but what I found i am not sure how to make work for me but here is my problem.

I have a database used to keep track of phones for multiple clients. What needs to be done is have a query that can be ran that will run against multiple databases on the same server. each database uses the same table name that I am looking at but the names are slightly different. I came up with this..

INSERT INTO `export db`.exportinfo2 SELECT * FROM (SELECT * FROM `export db'.tentantnames).users WHERE name = 'Caller ID:emergency' AND value > 0

What suppose to happen is from a table that has all the database names is is to got to each database and go into the table labeled users and run a where clause on the data then export results to a different database table I know the code needs to be dynamic but I am not sure how to make it dynamic and function. The table that has all the names for the databases is automatically created every few days.. I am not sure what else needs to be said without sounding like i repeat myself but i just need help making a dynamic query that uses a table premade as database names and run a where statement on the same named table in each database which have their name stored in a different table.

Answer 1

You should look into Synonyms. It can be used to fulfill your purpose