Fluxtail
Sign in Create account
Log Management Guides

How to Search for a Column Name in SQL

Find a column name in PostgreSQL, MySQL, SQL Server, or SQLite using metadata queries, with tips for exact matches and missing results.

By Fluxtail Engineering Updated

To search a column name in SQL, query the database’s metadata rather than scanning table rows. The query depends on the database engine: PostgreSQL and MySQL expose information_schema.columns, SQL Server has sys.columns, and SQLite exposes table metadata through pragma_table_xinfo. The examples below return object and column names without reading application data or changing the schema.

Use customer as a broad search term when you are unsure whether the field is named customer_id, customer_ref, or something else. Once you find candidates, search the full name. Confirm the database and schema, and check separately whether a hit is a table or view if the query does not show its object type.

PostgreSQL: search columns

Run this in the database you want to inspect:

SELECT
  table_schema,
  table_name,
  column_name,
  data_type
FROM information_schema.columns
WHERE column_name ILIKE '%customer%'
ORDER BY table_schema, table_name, column_name;

ILIKE is PostgreSQL’s case-insensitive pattern match. Replace the condition with column_name = 'customer_id' when you need that exact spelling. If the results are too broad, add AND table_schema = 'public' or another known schema.

In PostgreSQL 18, information_schema.columns includes accessible columns of tables and views in the current database. It omits system columns such as ctid. It also shows only columns the current user can access, so an empty result does not prove a column is absent. Confirm the connection’s database, schema, and account before drawing that conclusion. See the PostgreSQL columns reference and pattern-matching rules.

This is a metadata search, not a data search. Finding customer_id tells you where that column is defined; it does not tell you which rows contain a particular customer value.

MySQL: search the selected database

MySQL’s INFORMATION_SCHEMA.COLUMNS covers column metadata across databases on the server. Filter by TABLE_SCHEMA so the results correspond to the database you mean to inspect:

SELECT
  c.TABLE_SCHEMA,
  c.TABLE_NAME,
  t.TABLE_TYPE,
  c.COLUMN_NAME,
  c.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS AS c
JOIN INFORMATION_SCHEMA.TABLES AS t
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
 AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.COLUMN_NAME LIKE '%customer%'
  AND t.TABLE_TYPE IN ('BASE TABLE', 'VIEW')
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME;

DATABASE() uses the connection’s selected database. If none is selected, specify one explicitly, for example c.TABLE_SCHEMA = 'orders_db'. The join supplies BASE TABLE or VIEW, so a similarly named view is not mistaken for a stored table. To find an exact column, replace the LIKE condition with c.COLUMN_NAME = 'customer_id'.

MySQL’s matching behavior depends on the applicable collation; do not assume LIKE has the same case behavior as PostgreSQL’s ILIKE. Metadata is also limited by the account’s privileges. MySQL documents the fields in COLUMNS, object types in TABLES, and metadata privilege rules.

SQL Server: search user tables and views

In SQL Server, join column metadata to objects and schemas. Restrict the object type to user tables (U) and views (V), instead of treating every row in sys.columns as an application table:

SELECT
  s.name AS schema_name,
  o.name AS object_name,
  o.type_desc AS object_type,
  c.name AS column_name
FROM sys.columns AS c
JOIN sys.objects AS o
  ON o.object_id = c.object_id
JOIN sys.schemas AS s
  ON s.schema_id = o.schema_id
WHERE o.type IN ('U', 'V')
  AND c.name LIKE N'%customer%'
ORDER BY s.name, o.name, c.name;

Use o.type = 'U' if you want only user tables. Use c.name = N'customer_id' for an exact column name. The N prefix makes the search string a Unicode literal; it is appropriate for SQL Server’s sysname column names.

Run the query in each database you need to inspect: this query does not search every database on an instance. SQL Server also limits catalog metadata according to the caller’s permissions. Microsoft documents the objects represented by sys.columns and its metadata-visibility rule.

SQLite: search sqlite_schema and table_xinfo

SQLite does not provide the same INFORMATION_SCHEMA.COLUMNS interface. On SQLite 3.33.0 or newer, join each table or view in sqlite_schema to the table-valued pragma_table_xinfo function:

SELECT
  m.type AS object_type,
  m.name AS object_name,
  x.name AS column_name
FROM sqlite_schema AS m
JOIN pragma_table_xinfo(m.name) AS x
WHERE m.type IN ('table', 'view')
  AND x.name LIKE '%customer%'
ORDER BY m.name, x.name;

Change the condition to x.name = 'customer_id' for an exact match. table_xinfo includes generated and hidden columns that the narrower table_info pragma omits. The example inspects the main database schema; an attached database or temporary schema needs its own schema-aware query. SQLite documents table-valued PRAGMA functions and table_xinfo.

SQLite’s default LIKE ignores ASCII letter case, but it does not perform full Unicode case folding without an extension. That distinction matters if identifiers contain non-ASCII letters; see SQLite’s expression rules.

Exact matches, partial matches, and missing results

Start broad when the name is uncertain: %customer% finds a fragment anywhere in the column name. Then use = 'customer_id' to match the full name, subject to the database’s collation rules. A LIKE pattern containing customer_id has a trap: _ means “any one character,” not a literal underscore. For a literal identifier, equality is simpler than relying on each engine’s escape syntax.

The result identifies where a column is declared, not where its values are populated or how application code uses it. Check whether the hit is a table or a view before changing queries or migrations. A view may expose a renamed column that does not exist under that name in its underlying table.

If there are no rows:

  1. Confirm the active database. PostgreSQL and the SQL Server query above inspect the current database; MySQL’s example filters to the selected database.
  2. Check the schema or database filter. A column in archive will not appear if you restricted the search to public.
  3. Try a shorter fragment, such as %customer%, to catch a renamed or prefixed field.
  4. Check the account’s metadata permissions. PostgreSQL, MySQL, and SQL Server can hide objects the account cannot see.
  5. Confirm that the name is a database column at all. An application log field, JSON key, or alias in a query need not correspond to a physical column.

These SELECT examples inspect metadata. They do not alter tables, but they can still reveal schema names; share query results only with people authorized to see that metadata.

When the column name came from an application log

A log entry containing customer_id can point to a database column, a JSON property, or a name assigned by application code. Search the database metadata to test the database-column hypothesis, then inspect the query or mapping that emitted the log entry. Do not infer a schema migration solely from a log-field name.

Fluxtail can help investigate the relevant application logs; it does not inspect your database catalog or discover SQL columns. For the separate task of extracting fields reliably from log messages, see the log parsing guide.