Chris Widner Chris Widner - 1 year ago 78
MySQL Question

How do I find a table in MySQL with 2 specific columns in it

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.

Answer Source

Through the 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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download