How to Find All Tables with a Specific Column in MySQL (e.g., food_uid)

When working with large or legacy databases, it’s common to forget exactly where a specific column exists — especially if that column (like food_uid) is used widely for relationships, tracking, or categorization. Fortunately, MySQL provides a built-in way to explore your database schema without needing to open every table manually.

Here’s how to do it the right way.

🔍 The Reliable Way: Query INFORMATION_SCHEMA

MySQL includes a special database called INFORMATION_SCHEMA. It contains metadata about your actual databases — things like table names, column names, data types, and constraints. You can use it to programmatically find any column in any table.

To find all tables containing a column named food_uid, use this query:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'food_uid'
  AND TABLE_SCHEMA = 'your_database_name';

Replace 'your_database_name' with the name of your database.

✅ What This Query Does

• TABLE_NAME tells you which table the column appears in.
• COLUMN_NAME confirms the column name match (in case you're using LIKE or patterns).
• TABLE_SCHEMA filters only within your own database — important when working in shared environments.

💡 Additional Tips You Might Be Missing

Get Tables with Partial Column Match:
Useful if you’re unsure of the exact column name:

WHERE COLUMN_NAME LIKE '%food%'

Check If It’s a Foreign Key:
If you want to know whether food_uid is used as a foreign key, this query will help:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE COLUMN_NAME = 'food_uid'
  AND TABLE_SCHEMA = 'your_database_name';

Find Column Data Types:
Add DATA_TYPE to your SELECT if you want to inspect how it's declared:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
...

Case Sensitivity: MySQL column names are case-insensitive on most systems, but not always. If you're unsure, you can use:

WHERE LOWER(COLUMN_NAME) = 'food_uid'

⚠️ Considerations When Refactoring or Auditing

• Naming conventions matter. If food_uid shows up everywhere but isn't consistently named (foodId, foodID, food_uid_fk), your searches may miss important tables.
• If you're planning schema changes, always cross-check foreign key constraints before renaming or removing any column.
• Use tools like MySQL Workbench, Adminer, or DBeaver if you prefer a GUI to view schema structure — but the SQL method is more precise and scriptable.

🧩 Finally

Finding all tables with a specific column is more than just a SQL trick — it’s part of understanding your data model. Especially in large applications, this can be crucial before:

• Refactoring database structure
• Migrating data
• Debugging foreign key issues
• Building new features on top of existing relationships

With just a few lines of SQL, you can get a full picture of where and how your data is linked.

Support Us

Share to Friends