How to search a column name from a MySQL database?

Question

I am running a huge database with so many tables and tables are having so many columns.

My DB is MySQL and I have to search for a particular column.

Is there a way available in MySQL to search a column name from all tables in a database?

If you need to search a column name then your table design is most probably wrong. Can you change it and if so, do you need a hint how to? — juergen d, Mar 18 '16 at 09:54
@lad2025 - I have so many tables in my db and i have to search for a particular column and its really hard to go to every table and check the column. If it was easy, i didnt have posted my question here :) — Joomler, Mar 18 '16 at 10:06
Did I say it was easy/hard. Do not take it personal. Question is question, it is obvious duplicate. If you think that I am wrong you could cast reopen vote or add question http://meta.stackoverflow.com — Lukasz Szozda, Mar 18 '16 at 10:08
@lad2025 I appreciate you replied and seriously I havent taken it personally, I was just trying to explain my situation. No hard feeling. — Joomler, Mar 18 '16 at 10:13

score 43 · Accepted Answer · edited Aug 16 '21 at 13:51

43

Query

select table_name, column_name 
from information_schema.columns 
where column_name like '%search_keyword%'; -- change search_keyword accordingly

Or if you want to search for exact column name then no need of LIKE.

where column_name = 'column_name_to_be_find';

edited Aug 16 '21 at 13:51

Mauricio

answered Mar 18 '16 at 09:54

Ullas

thanks for the quick reply @Ullas but I have so many tables in my db and i have to search for a particular column and its really hard to go to every table and check the column name. – Joomler Mar 18 '16 at 10:08
@RishiVishwakarma : You don't need to go for each table to search for the column name. – Ullas Mar 18 '16 at 10:11

score 15 · Answer 2 · answered Mar 18 '16 at 09:54

15

Try this:

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('columnA','ColumnB')
AND TABLE_SCHEMA='YourDatabase';

answered Mar 18 '16 at 09:54

Alok Gupta

2 Answers2