Saturday, July 5, 2014

MySQL: How to find tables that are using a certain column name?

This SQL helps you list the tables with a certain column name. As you can see in the script, you can also use comma-separated multiple column names.

    SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('column_name1', 'column_name2')
    AND TABLE_SCHEMA='database_name'

You may also use "COLUMN_NAME LIKE" to find a closer match of something you are looking for such as:

    SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME LIKE 'column_name%'
    AND TABLE_SCHEMA='database_name'