Introduction

When you need to find every MySQL table that contains a given column name, the right place to look is INFORMATION_SCHEMA.COLUMNS. That metadata table lets you query schema structure directly instead of inspecting tables one by one.

Query INFORMATION_SCHEMA.COLUMNS

The most direct query is:

1SELECT
2    TABLE_SCHEMA,
3    TABLE_NAME,
4    COLUMN_NAME
5FROM INFORMATION_SCHEMA.COLUMNS
6WHERE COLUMN_NAME = 'desired_column_name';

This returns every table, across all visible databases, where the column name matches exactly. It is useful when you are tracing a field during refactoring, auditing duplicated schema design, or checking migration impact.

Restrict the Search to One Database

In many cases, you only want matches from the current application schema. Add a TABLE_SCHEMA filter so the search stays focused.

1SELECT
2    TABLE_NAME,
3    COLUMN_NAME
4FROM INFORMATION_SCHEMA.COLUMNS
5WHERE TABLE_SCHEMA = 'app_db'
6  AND COLUMN_NAME = 'desired_column_name';

This is usually the safer production query because it avoids unrelated system schemas and neighboring databases on the same server.

Search for Multiple Possible Column Names

Sometimes the real task is broader, such as finding every table that contains either created_at or updated_at. In that case, use IN.

1SELECT
2    TABLE_SCHEMA,
3    TABLE_NAME,
4    COLUMN_NAME
5FROM INFORMATION_SCHEMA.COLUMNS
6WHERE TABLE_SCHEMA = 'app_db'
7  AND COLUMN_NAME IN ('created_at', 'updated_at')
8ORDER BY TABLE_NAME, COLUMN_NAME;

This is helpful when normalizing legacy schemas or locating variant field names before a migration.

Find Tables That Contain All Required Columns

If you need tables that contain several columns together, group and count the matches.

1SELECT
2    TABLE_SCHEMA,
3    TABLE_NAME
4FROM INFORMATION_SCHEMA.COLUMNS
5WHERE TABLE_SCHEMA = 'app_db'
6  AND COLUMN_NAME IN ('first_name', 'last_name', 'email')
7GROUP BY TABLE_SCHEMA, TABLE_NAME
8HAVING COUNT(DISTINCT COLUMN_NAME) = 3;

That query answers a different question from simple presence. It finds tables that contain the full required set.

Use the Results for Follow-Up Inspection

After identifying candidate tables, you may want to inspect the full column layout to confirm types, keys, or nullability.

1SELECT
2    COLUMN_NAME,
3    DATA_TYPE,
4    IS_NULLABLE,
5    COLUMN_KEY
6FROM INFORMATION_SCHEMA.COLUMNS
7WHERE TABLE_SCHEMA = 'app_db'
8  AND TABLE_NAME = 'users'
9ORDER BY ORDINAL_POSITION;

This helps when the same column name appears in several places but is not used consistently.

If you are exploring legacy schemas, it can also help to search with naming patterns such as LIKE '%_id' or LIKE 'created_%'. Exact-name searches are better for precision, but pattern searches are useful during discovery work.

A Practical Rule for Schema Discovery

For schema-discovery tasks, INFORMATION_SCHEMA should be your default tool. It is easier to automate, easier to review, and much safer than ad hoc manual inspection. Once the query is right, you can reuse it for documentation, migrations, and audit checks.

Common Pitfalls

• Forgetting to filter by TABLE_SCHEMA and getting matches from unrelated databases.
• Searching for one column name when the real requirement is a set of columns used together.
• Assuming the same column name implies the same type, nullability, or business meaning.
• Using table-by-table manual inspection instead of metadata queries.
• Ignoring case and naming conventions in mixed legacy schemas.

Summary

• INFORMATION_SCHEMA.COLUMNS is the standard place to find MySQL tables by column name.
• Add TABLE_SCHEMA filters to keep the results relevant.
• Use IN to search for several column names and GROUP BY with HAVING to require a full set.
• Follow up with column metadata inspection when names alone are not enough.
• Metadata queries are the cleanest way to explore schema structure in MySQL.