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)
Published on 31-Dec-2019 07:33:54
