How to find tables with a specific column name in MySQL?

To find column names, use information_schema.columns. Following is the syntax −

select distinct table_name
from information_schema.columns
where column_name like '%yourSearchValue%'
and table_schema=database();

Let us implement the above syntax in order to find column names across various table. Here, we want only table names with a specific column name word “Client” −

mysql> select distinct table_name
   from information_schema.columns
   where column_name like '%Client%'
   and table_schema=database();

This will produce the following output −

+----------------+
| table_name     |
+----------------+
| demotable449   |
| demotable450   |
| demotable461   |
| demotable517   |
| demotable529   |
| demotable534   |
| demotable537   |
| demotable543   |
| demotable547   |
+----------------+
9 rows in set (1.19 sec)

Now, let us check any of the table and look for word with “Client” column name −

AmitDiwan

Updated on: 09-Oct-2019

891 Views

Kickstart Your Career

Get certified by completing the course

Get Started