Get all the tables from a MySQL database having a specific column, let’s say xyz?

Let’s say we have a database “web” and we need to get all the tables having a specific column ’StudentFirstName’.

For this, below is the query −

mysql> select myColumnName.table_name from
information_schema.columns myColumnName
where myColumnName.column_name = 'StudentFirstName' and table_schema='web';

This will produce the following output −

+---------------+
| TABLE_NAME    |
+---------------+
| demotable109  |
| demotable297  |
| demotable335  |
| demotable395  |
| demotable418  |
| demotable425  |
| demotable436  |
+---------------+
7 rows in set (0.14 sec)

Therefore, the above tables have one of the column names as “StudentFirstName”.

Let us check the description of any of the table to look for the column name ‘StudentFirstName’ −

mysql> desc demotable297;

This will produce the following output displaying one of the column names as “StudentFirstName” −

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| StudentId        | int(11)      | YES  |     | NULL    |       |
| StudentFirstName | varchar(100) | YES  |     | NULL    |       |
| StudentLastName  | varchar(100) | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

AmitDiwan

Updated on: 27-Sep-2019

139 Views

Kickstart Your Career

Get certified by completing the course

Get Started