Finding the table containing a column name in MySQL

Published on 2013-09-25 by John Collins. Please follow me on Twitter for more: 

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