Generate dynamic query based on changing table names
Posted on 2011-10-04
I have a group of tables in my db that have this naming convention:
A new table is added regularly, the next one will be Table127.
I need to query these tables as if they were one table, and currently i simply do this by hardcoding table names:
select * from Table123
select * from Table124
select * from table 125
select * from Table126;
my question is this: i would simply like to be able to get the list of table names (SELECT table_name FROM information_schema.tables WHERE table_name like 'Table1%'), and then iterate through the results to dynamically create the query above.
Best way to do this?