Finding a column inside a database is one of those tasks that looks trivial until you inherit a system with 400 tables, three naming conventions, and no documentation. The good news is that every major relational database engine stores its own structure as queryable data, so a SQL query to find column in database is usually a single statement against a system catalog or an INFORMATION_SCHEMA view. This guide walks through the standard approach, the engine-specific shortcuts, and the more advanced cases such as searching for a column value across every table or locating tables that are missing an expected field Most people skip this — try not to..
Why Column Search Matters in Real Projects
Schema discovery is not only a beginner exercise. It shows up constantly in production work:
- Onboarding to a legacy system where table and column names were chosen by people who left years ago.
- Migrating between engines, such as moving from MySQL to PostgreSQL, where you must confirm which columns actually exist before writing conversion scripts.
- Auditing sensitive data, for example locating every column that stores an email address, phone number, or national ID before applying masking.
- Debugging ORM errors like Unknown column or column does not exist, which are almost always a naming or case-sensitivity mismatch.
- Building dynamic SQL that loops over tables and only touches the ones containing a specific field.
In all of these situations, guessing by browsing a GUI is slow and error-prone. Querying the catalog is faster, scriptable, and repeatable.
The Standard Answer: INFORMATION_SCHEMA
INFORMATION_SCHEMA is the ANSI SQL-standard set of views that describes a database's metadata. It is supported by MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, and many others, which makes it the most portable place to start.
The key view is INFORMATION_SCHEMA.COLUMNS. To list every column in the current database:
SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
IS_NULLABLE,
COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY TABLE_NAME, ORDINAL_POSITION;
To check whether one specific column exists anywhere in the schema, filter on the name:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'customer_id'
ORDER BY TABLE_SCHEMA, TABLE_NAME;
A few practical notes on this query:
TABLE_SCHEMAis not always the database name. In SQL Server it usually means the schema owner (dbo,sales), while the database name lives inTABLE_CATALOG. In MySQL and PostgreSQL,TABLE_SCHEMAtypically maps to the database or schema you are connected to.COLUMN_NAMEcomparison is case-sensitive in some engines. PostgreSQL folds unquoted identifiers to lowercase, soWHERE COLUMN_NAME = 'Customer_ID'will silently return nothing. PreferLOWER(COLUMN_NAME) = 'customer_id'when you are unsure.- Use wildcards for fuzzy searching.
WHERE COLUMN_NAME LIKE '%date%'is the fastest way to find every timestamp-like field in a large schema.
Useful Companion Columns
Beyond the name and type, INFORMATION_SCHEMA.COLUMNS exposes details that answer follow-up questions immediately:
ORDINAL_POSITION— the column's order in the table.IS_NULLABLE— whether NULLs are allowed.COLUMN_DEFAULT— the default expression.CHARACTER_MAXIMUM_LENGTHandNUMERIC_PRECISION— size and precision.CONSTRAINTinformation is not here; join toINFORMATION_SCHEMA.KEY_COLUMN_USAGEandTABLE_CONSTRAINTSto find primary or foreign keys.
Engine-Specific Queries
Each database ships its own catalog views and helper commands. These are often shorter and expose more detail than the standard views Turns out it matters..
SQL Server
-- Option 1: catalog views
SELECT
s.name AS schema_name,
t.name AS table_name,
c.name AS column_name,
ty.name AS data_type,
c.max_length,
c.is_nullable
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
WHERE c.name LIKE '%order%'
ORDER BY t.name, c.column_id;
-- Option 2: built-in procedure
EXEC sp_columns @table_name = 'Orders';
-- Option 3: quick object resolution
SELECT OBJECT_NAME(object_id), *
FROM sys.columns
WHERE name = 'TotalAmount';
SQL Server also offers sys.all_columns, which includes columns from system objects, and the undocumented-but-handy sp_help 'schema.table'.
MySQL and MariaDB
-- Describe a single table
DESCRIBE orders;
-- or
SHOW COLUMNS FROM orders;
-- Search across the whole server
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%email%';
-- One-liner from the shell
SHOW COLUMNS FROM orders FROM your_database LIKE 'status%';
MySQL treats TABLE_SCHEMA as the database name, which makes cross-database searches straightforward: drop the TABLE_SCHEMA filter to search every database the account can see But it adds up..
PostgreSQL
-- Standard view
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders';
-- Native catalog (more detail, includes dropped-column markers)
SELECT attrelid::regclass AS table_name,
attname AS column_name,
format_type(atttypid, atttypmod) AS data_type,
attnotnull AS not_null
FROM pg_attribute
WHERE attnum > 0
AND NOT attisdropped
AND attrelid = 'public.orders'::regclass;
-- psql meta-command
\d+ public.orders
The pg_attribute query is the one to reach for when you need exact type rendering, including array types and user-defined domains Small thing, real impact..
Oracle
SELECT table_name, column_name, data_type, data_length, nullable
FROM all_tab_columns
WHERE owner = 'APP_SCHEMA'
AND column_name LIKE '%AMOUNT%';
-- Columns in tables you own
SELECT * FROM user_tab_columns WHERE table_name = 'ORDERS';
-- Quick describe in SQL*Plus or SQL Developer
DESC app_schema.orders
Remember that Oracle stores unquoted identifiers in uppercase, so literal comparisons must use uppercase strings Still holds up..
SQLite
PRAGMA table_info('orders');
-- or, for all tables
SELECT name AS table_name, sql AS create_statement
FROM sqlite_master
WHERE type = 'table';
Finding a Column Value Across All Tables
Knowing a column exists is only half the job. Sometimes you need to know which rows contain a specific value, for example tracking down where a stray customer ID is referenced before deleting it. Because table and column names cannot be parameterized in standard SQL, this requires dynamic SQL That's the part that actually makes a difference..
-- SQL Server template
DECLARE @search_value VARCHAR(50) = 'CUST-1042';
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N'
SELECT '