How to identify a column with its existence in all the tables with MySQL?

To identify a column name, use INFORMATION_SCHEMA.COLUMNS in MySQL. Here’s the syntax −

select table_name,column_name from INFORMATION_SCHEMA.COLUMNS where table_schema = SCHEMA() andcolumn_name='anyColumnName';

Let us implement the above query in order to identify a column with its existence in all tables. Here, we are finding the existence of column EmployeeAge −

mysql> select table_name,column_name    FROM INFORMATION_SCHEMA.COLUMNS    WHERE table_schema = SCHEMA()    AND column_name='EmployeeAge';

This will produce the following output displaying the tables with specific column “EmployeeAge” −

+---------------+-------------+ | TABLE_NAME    | COLUMN_NAME | +---------------+-------------+ | demotable1153 | EmployeeAge | | demotable1297 | EmployeeAge | | demotable1303 | EmployeeAge | | demotable1328 | EmployeeAge | | demotable1378 | EmployeeAge | | demotable1530 | EmployeeAge | | demotable1559 | EmployeeAge | | demotable1586 | EmployeeAge | | demotable1798 | EmployeeAge | | demotable1901 | EmployeeAge | | demotable511  | EmployeeAge | | demotable912  | EmployeeAge | +---------------+-------------+ 12 rows in set (0.00 sec)

To prove, let us check the description of any of the above table −

mysql> desc demotable1153;

This will produce the following output displaying existence of EmployeeAge column in demotable1153 −

+--------------+-------------+------+-----+---------+----------------+ | Field        | Type        | Null | Key | Default | Extra          | +--------------+-------------+------+-----+---------+----------------+ | EmployeeId   | int(11)     | NO   | PRI | NULL    | auto_increment | | EmployeeName | varchar(40) | YES  | MUL | NULL    |                | | EmployeeAge  | int(11)     | YES  |     | NULL    |                | +--------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)