Query from a Table, That's derived from a Information_Schema.Tables Result

Question

I'm having to query from a Database that has more than 50 tables - all having the same structure (I know, Horrid Database design from a legacy project that's been in production for 5+ years!). To do this, I've queried the information_Schema like below:

    SELECT 
        TABLE_NAME 
    FROM 
        INFORMATION_SCHEMA.tables 
    WHERE TABLE_SCHEMA =
        'projectdatabase'   
            AND 
        TABLE_NAME LIKE '%_usertable'

Which provides me the 50 or so tables that I need, in a result. Now, I will need to query columns from within each of those tables for example, PRODUCT_ID. In doing so, I've attempted:

    SELECT 
        projectdatabase.userTable.PRODUCT_ID
    FROM (
        SELECT 
            TABLE_NAME as userTable 
        FROM 
            INFORMATION_SCHEMA.tables 
        WHERE TABLE_SCHEMA =
            'projectdatabase'   
                AND 
            TABLE_NAME LIKE '%_usertable'
    ) AS userTables

Now this obviously doesn't work due to MySQL not treating the 'userTable' as Database table - but what I'm attempting to do, is query * FROM {tablename} where tablename was the information_schema query result.

I could attempt to split this up in PHP, although I'm eagerly wondering if this was possible to do within MySQL.

Answer 1

You have to use prepared statement:

 SET @sql:=(SELECT GROUP_CONCAT(
            CONCAT("SELECT PRODUCT_ID FROM ", TABLE_NAME) SEPARATOR " UNION ")
   FROM INFORMATION_SCHEMA.tables 
   WHERE TABLE_SCHEMA = 'projectdatabase'   
            AND TABLE_NAME LIKE '%_usertable');
 PREPARE stmt FROM @sql;
 EXECUTE stmt;

EDIT: You can also set SET SESSION group_concat_max_len = 1000000;, but doing all in SQL is not a thing to do. Your table list is kind of a constant, and the query would be in a better place in your PHP code.