Sql Query To Find Column In Database

5 min read

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:

  1. TABLE_SCHEMA is not always the database name. In SQL Server it usually means the schema owner (dbo, sales), while the database name lives in TABLE_CATALOG. In MySQL and PostgreSQL, TABLE_SCHEMA typically maps to the database or schema you are connected to.
  2. COLUMN_NAME comparison is case-sensitive in some engines. PostgreSQL folds unquoted identifiers to lowercase, so WHERE COLUMN_NAME = 'Customer_ID' will silently return nothing. Prefer LOWER(COLUMN_NAME) = 'customer_id' when you are unsure.
  3. 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_LENGTH and NUMERIC_PRECISION — size and precision.
  • CONSTRAINT information is not here; join to INFORMATION_SCHEMA.KEY_COLUMN_USAGE and TABLE_CONSTRAINTS to 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 '
Just Made It Online

Just Released

Try These Next

A Natural Next Step

Thank you for reading about Sql Query To Find Column In Database. 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