Skip to content

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';
See also  ERROR 1017 (HY000): Can't find file: './....frm' (errno: 13) - MySQL error. How to fix?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.