Similar to How to find all the tables in MySQL with specific column names in them? I would like to find the table with 2 specific columns, not either or.
I've tried combining with AND but no dice.
For instance, I want to search the database for the specific tables that contain both CategoryID and LotNumber columns.
information_schema.columns table, grouping the matching columns by table and returning only those with number equal to 2:
SELECT table_name FROM information_schema.columns WHERE (column_name = 'colname1' OR column_name = 'colname2') [AND table_schema = 'dbname'] GROUP BY table_name HAVING count(*) = 2;