How to list all tables that contain a specific column name?
In this small post we are going to see how to list all tables that contain a specific column name. In MySQL sometimes you want to list down all tables names in the whole database that have a specific column.
Syntax
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'column_name'
AND TABLE_SCHEMA='schema';Example
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME ='employee_id'
AND TABLE_SCHEMA='payroll';This query returns all the tables that contain the column name ‘employee_id‘ from the payroll schema.
Multiple column names can be filtered with IN clause.
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME in('employee_id', 'salary')
AND TABLE_SCHEMA='payroll';
