0

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.

Community
  • 1
  • 1
El Tigre
  • 172
  • 1
  • 1
  • 11

2 Answers2

1

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;
Cavaz
  • 2,996
  • 24
  • 38
0

You can try something like :

SELECT * from TableName where obj1 = "obj1" and obj2= "obj2"

this is an example.

Let me know how it worked :)

0ndre_
  • 3,577
  • 6
  • 26
  • 44
  • I appreciate the quick response, but perhaps I didn't ask my question well. I'm searching to find the table that has 2 specific columns in that same table. – El Tigre Oct 14 '16 at 21:11
  • Ohh I see I did not get it for the first time :) – 0ndre_ Oct 14 '16 at 21:12