How can I return a list of all tables in a database with column hostname that contain hostname=hostA

Question

I can get all the tables containing column 'hostname' using:

select select table_name from information_schema.columns 
where column_name='hostname';

If I knew the names of all the tables I could use a union like:

SELECT * FROM ((SELECT hostname FROM table1)  
    UNION (SELECT hostname FROM table2)
    ...
    UNION (SELECT hostname FROM tableN)) AS hosttable where hostname = 'hostA';

But I don't know how to combine the above two concepts without using an external script or stored procedure.

Answer 1

SQL queries must list the tables and columns explicitly. You can't write a query that takes the name of a table from the result of another column searched in the same query.

The solution is the one you have already found: write one SQL query against the information schema to get a list of table names, and then use those results to build a second SQL query, interpolating the table names into the appropriate place in the query.

You can do this in a stored procedure with PREPARE and EXECUTE, or you can do it in application code.