How to get all the tables' name having specific column name using any query?

Question

Suppose I have 4 tables, named:-

Now three tables have a field called users_id

Is there are query which can tell me all the tables which has a field with column name users_id?

I don't even have any idea if it is possible or not.

duplicate http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them?rq=1 — RST, May 05 '16 at 07:49

Ankit Agrawal · Accepted Answer · 2016-05-05T07:51:11.603

1

Get table name from schema like below

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME ='users_id'
AND TABLE_SCHEMA='db_name';

edited May 05 '16 at 07:51

answered May 05 '16 at 07:46

Ankit Agrawal

Its returning blank result – Saswat May 05 '16 at 07:49
1

pass your db_name in table_schema ..it will surely work – Ankit Agrawal May 05 '16 at 07:51

score 0 · Answer 2 · answered May 05 '16 at 07:59

0

Try This Query

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME ='Your Column Name' AND TABLE_SCHEMA='Your Database Name'

answered May 05 '16 at 07:59

Divyesh

2 Answers2