SQL list all tables with certain column names

Question

I'd like to make a SQL query to a MySQL 5.6 server to return me all the tables that contains AT LEAST the following column names:

field1 
field2

I've seen examples here but they only cover having exactly one column name or one between many possible.

For example, if I have these 4 tables:

TABLE1
field1 field2 field3

TABLE2
field2 field4

TABLE3
field1 field3

TABLE4
field1 field2

Since I'm querying the tables that have AT LEAST "field1" AND "field2", the result should be:

TABLE1
TABLE4

I tried doing a self join with the INFORMATION_SCHEMA.COLUMNS table but I can't get it right...

Thanks for your help

See http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them — Alex, Apr 30 '15 at 14:52
This is not correct, because it will show those with "field1" OR "field2" and I want both of them. I already tested before — Imanol Barba Sabariego, Apr 30 '15 at 14:57

Zohar Peled · Accepted Answer · 2015-04-30T15:00:32.810

1

Try this:

SELECT DISTINCT T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1
INNER JOIN INFORMATION_SCHEMA.COLUMNS T2 ON(T1.TABLE_NAME = T2.TABLE_NAME)
WHERE T1.COLUMN_NAME = 'field1'
AND T2.COLUMN_NAME = 'field2'

Note: code was written directly here, not tested.

edited Apr 30 '15 at 15:00

answered Apr 30 '15 at 14:49

Zohar Peled

79,642
10
69
121

Ah you are right! I did this but I forgot putting T1 in SELECT DISTINCT T1.TABLE_NAME :_D Thank you!!! – Imanol Barba Sabariego Apr 30 '15 at 14:56
Small typo on T2..COLUMN_NAME though – Imanol Barba Sabariego Apr 30 '15 at 14:57

score 0 · Answer 2 · answered Apr 30 '15 at 14:54

0

Use this:

SELECT DISTINCT TABLE_NAME
FROM information_schema.COLUMNS 
WHERE COLUMN_NAME = 'field1' OR COLUMN_NAME = 'field2'

This should return EVERY table name which has a column named 'field1' or 'field2'.

answered Apr 30 '15 at 14:54

Filipe Paulo

134
6

1

OP wants every table to have both columns, not either column. – Eilidh Apr 30 '15 at 14:56

SQL list all tables with certain column names

2 Answers2