Search For Column Name In Sql

5 min read

Finding a specific column name across a database is one of those tasks that sounds simple until you are staring at a schema with hundreds of tables. Whether you are debugging a legacy system, documenting an unfamiliar schema, or hunting for where sensitive data like email or ssn might be hiding, knowing how to efficiently search for column names in SQL is a fundamental skill for developers, DBAs, and data analysts Small thing, real impact..

Unlike searching for data inside a column, searching for the column definition itself requires querying the database metadata, often referred to as the Information Schema or System Catalogs. Which means the exact syntax varies significantly between database engines like MySQL, PostgreSQL, SQL Server, Oracle, and SQLite. This guide provides a comprehensive, engine-specific breakdown of how to locate columns by name, partial matches, and even across specific schemas Not complicated — just consistent..

Understanding the Information Schema

Before diving into specific queries, it helps to understand where this data lives. The most relevant view for this task is INFORMATION_SCHEMA.The SQL standard defines the **INFORMATION_SCHEMA** as a set of read-only views that provide information about all the tables, views, columns, and procedures in a database. COLUMNS.

Most modern relational databases (MySQL, PostgreSQL, SQL Server, MariaDB) adhere to this standard, making the basic query structure remarkably consistent. Even so, proprietary systems like Oracle use their own data dictionary views (e.In real terms, g. , ALL_TAB_COLUMNS), and lightweight engines like SQLite require querying the sqlite_master table or using PRAGMA commands.

The standard columns available in INFORMATION_SCHEMA.Day to day, * IS_NULLABLE: Whether the column accepts NULLs (YES/NO). * COLUMN_DEFAULT`: The default value expression.

  • TABLE_NAME: The table containing the column.
  • COLUMN_NAME: The name of the column. , VARCHAR, INT, DATETIME).
  • TABLE_SCHEMA: The schema/owner name.
  • DATA_TYPE: The data type (e.* CHARACTER_MAXIMUM_LENGTH: Max length for string types. Because of that, g. COLUMNS` typically include:
  • TABLE_CATALOG: The database name.
  • ORDINAL_POSITION: The position of the column in the table definition.

Searching in MySQL and MariaDB

MySQL and its fork MariaDB offer the most straightforward implementation of the standard. Because INFORMATION_SCHEMA is a database itself, you can query it directly from any context, provided you have the SELECT privilege on it Most people skip this — try not to..

Exact Match Search

To find every table containing a column named exactly created_at:

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

Partial Match (Wildcard Search)

This is the most common use case. Use the LIKE operator with % wildcards to find columns containing a specific string, such as "email" or "address" That's the part that actually makes a difference..

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

Filtering by Database (Schema)

On a server hosting multiple databases, you will likely want to restrict the search to a specific database to avoid noise and improve performance Easy to understand, harder to ignore..

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'my_application_db'
  AND COLUMN_NAME LIKE '%user_id%';

Performance Tip: In MySQL, querying INFORMATION_SCHEMA can be slow on instances with thousands of tables because it often requires opening table definition files (.frm or .sdi files). Adding TABLE_SCHEMA to the WHERE clause helps the optimizer significantly Easy to understand, harder to ignore. Took long enough..

Searching in PostgreSQL

PostgreSQL fully supports INFORMATION_SCHEMA, but it also offers a more powerful, native system catalog: pg_catalog. Many experienced Postgres DBAs prefer pg_catalog because it exposes more internal details (like OIDs, compression settings, and statistics) and is often faster for complex metadata queries.

Easier said than done, but still worth knowing.

Using the Standard Information Schema

The syntax is nearly identical to MySQL:

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE column_name ILIKE '%email%'; -- ILIKE is case-insensitive

Note the use of ILIKE for case-insensitive matching, a Postgres-specific extension.

Using pg_catalog (Advanced)

For deeper inspection, join pg_attribute (columns) with pg_class (tables) and pg_namespace (schemas). This query excludes system columns (like ctid, tableoid) by filtering attnum > 0 and dropped columns with NOT attisdropped.

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       a.attname AS column_name,
       pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE a.attnum > 0
  AND NOT a.attisdropped
  AND c.relkind IN ('r', 'v', 'm', 'f', 'p') -- tables, views, materialized views, foreign tables, partitioned tables
  AND a.attname ILIKE '%email%'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema') -- exclude system schemas
ORDER BY n.nspname, c.relname, a.attnum;

Searching in SQL Server (T-SQL)

Microsoft SQL Server supports INFORMATION_SCHEMA.COLUMNS, but the native system catalog views (sys.tables, sys.columns, sys.schemas) are generally preferred for performance and because they expose SQL Server specific features (like is_identity, is_computed, is_sparse).

Standard Approach

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%order_id%';

Native System Catalog Approach (Recommended)

This method is faster and allows you to filter by object type (User Tables vs Views) easily.

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.precision,
    c.scale,
    c.is_nullable,
    c.is_identity
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE c.name LIKE '%order_id%'
  AND t.is_ms_shipped = 0 -- Exclude system tables
ORDER BY s.name, t.name;

Searching Across All Databases on an Instance

A common requirement for DBAs is searching every database on a server. This requires dynamic SQL or the undocumented sp_MSforeachdb stored procedure.

EXEC sp_MSforeachdb '
USE [?];
IF DB_ID() > 4 -- Skip system databases (master, tempdb, model, msdb)
BEGIN
    SELECT 
        ''?'' AS database_name,
        s.name AS schema_name,
        t.name AS table_name,
        c.name AS column_name
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE c.name LIKE ''%customer_id%'
Brand New

Straight to You

Worth Exploring Next

From the Same World

Thank you for reading about Search For Column Name In Sql. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home