Finding the table containing a column name in MySQL

Inspected the MySQL meta data to find the table in a database that contains a given column name.

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

John Collins

I have been writing about web technology and software development since 2001. I am the developer of the Alpha Framework for PHP, and the five.today personal productivity app. I love open source, technology, and economics.