Finding the table containing a column name in MySQL

Published at 2013-09-25 10:44:15

Sometimes when you are working with a legacy database schema that you are unfamiliar with, you are confronted with column names in error messages but not the table name that contains the column in question. An example of this is the 1048 error in MySQL, where some code has attempted to write a record to the database with a null value for a column that contains a NOT NULL constraint:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'ColumnName' cannot be null

Great, but what table contains the column "ColumnName"? Answering that question can be particularly hard if there are many tables in the database schema.

Luckily you can query the MySQL meta data to find out:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('ColumnName')
    AND TABLE_SCHEMA='DatabaseName';