Search code examples
mysqlsqlselectinformation-schema

How to find all tables that contain columnA and columnB

I want to achieve something like:

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'columnA'
AND COLUMN_NAME LIKE 'columnB';

But this specific query will return 0 coincidences.

Solution

  • Each row in information_schema.columns represents a single column - it can't be both columnA and columnB. One approach could be to count the number of such columns in a table and check which return two results:

    SELECT   table_name
FROM     information_schema.columns
WHERE    column_name IN ('COLUMNA', 'COLUMNB')
GROUP BY table_name
HAVING   COUNT(*) = 2