How to Find All Tables Containing a Specific Column Name

Posted by Kyle Hankinson September 15, 2022

---

When working with an unfamiliar database, you often need to find which tables contain a specific column. Every database provides system catalogs to search this information.

MySQL

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'email'
    AND TABLE_SCHEMA = 'your_database';

For a partial match:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%email%'
    AND TABLE_SCHEMA = 'your_database';

PostgreSQL

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE column_name = 'email'
    AND table_schema NOT IN ('pg_catalog', 'information_schema');

Or using PostgreSQL's own catalog:

SELECT c.relname AS table_name, a.attname AS column_name
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE a.attname = 'email'
    AND n.nspname = 'public'
    AND a.attnum > 0
    AND NOT a.attisdropped;

SQL Server

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'email';

Or using SQL Server's system views:

SELECT t.name AS table_name, c.name AS column_name, ty.name AS data_type
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE c.name = 'email';

Oracle

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME = 'EMAIL';

Note: Oracle stores metadata in uppercase by default. Use uppercase in your search or add UPPER():

WHERE UPPER(COLUMN_NAME) = 'EMAIL'

To search only your own tables:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME = 'EMAIL';

Searching Views Too

The INFORMATION_SCHEMA.COLUMNS view in MySQL, PostgreSQL, and SQL Server includes both tables and views by default. To filter:

-- Tables only
SELECT * FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
    ON c.TABLE_NAME = t.TABLE_NAME
    AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.COLUMN_NAME LIKE '%email%'
    AND t.TABLE_TYPE = 'BASE TABLE';

Quick Reference

Database	System Catalog
MySQL	INFORMATION_SCHEMA.COLUMNS
PostgreSQL	information_schema.columns or pg_attribute
SQL Server	INFORMATION_SCHEMA.COLUMNS or sys.columns
Oracle	ALL_TAB_COLUMNS / USER_TAB_COLUMNS

Using SQLPro Studio

SQLPro for MSSQL's sidebar includes a search field that lets you filter schema objects as you type. Instead of writing these catalog queries, just type the column name you are looking for and SQLPro for MSSQL will show matching tables instantly.

---