CODEWITHSUNDEEP
mysqlstored-procedures
NikoCon
NikoCon

Reputation: 99

MySQL select statement from n database inside stored proc

After some digging, it looks like a stored procedure is the way to go here but unsure if this is the best solution. I think there is quite a lot to what I am trying to achieve, but will do my best to simplify my problem.

I have a bunch of client databases, all in the exact same structure and all with same table names (legacy issue we inherited which is actively being restructured, but until then I need to take this approach).

What I'd like to do is loop through all these db's and do something like:

SELECT * FROM my_db_a.table1

SELECT * FROM my_db_b.table1

SELECT * FROM my_db_c.table1

I understand it is not possible to call a stored procedure from within a view. I was looking for info on for loops within mysql without needing to used a stored procedure but it doesn't look like this is a good approach or even possible for what I am trying to achieve - so a bit unsure how to proceed and find the most efficient solution to this problem.

Thanks in advance for any help.

Upvotes: 0

Views: 23

Answers (1)

dcp
dcp

Reputation: 55449

Assuming you can get to all the databases from the same SQL connection, you could just use a UNION ALL between each SELECT. This approach would allow you to get the data back from all your database tables in a single result set.

SELECT * FROM my_db_a.table1
UNION ALL
SELECT * FROM my_db_b.table1
UNION ALL
SELECT * FROM my_db_c.table1

Upvotes: 1

Related Questions