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';