Select all rows from all tables where column name equals given value

Question

Is there a way to select all the rows across all the tables where the column name matches a certain condition? I know how to select all tables that contain the given column name:

SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'customer_id'

Is there a way to join tables using the table name pulled out? For example, something that works as this (clearly incorrect) query tries to.

SELECT * FROM (SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'customer_id') WHERE customer_id = 1

Not that I know of and in general, how would such a thing be implemented? It is quite possible that some name is used for a column name in many different tables and that in each case the data type is different, which would seem to pose a problem. — Kevin, Aug 31 '11 at 14:43

score 2 · Accepted Answer · edited May 23 '17 at 12:29

Not in plain SQL. You can't use the results of one query as a table/field name in an outer query. In your non-working example, you'd get a result set that looks like

+---------------+
| TABLE_NAME    |
+---------------+
| name_of_table |
+---------------+

from which you then try to filter on a customer_id field, which doesn't exist in the inner result.

You can build a query statement dynamically in a stored procedure, and execute that statement as detailed here: How To have Dynamic SQL in MySQL Stored Procedure

However, needing such dynamic queries is usually a sign of bad table design.

Select all rows from all tables where column name equals given value

1 Answers1