Introduction

Listing every column in a table is a routine step during migrations, debugging, and query generation. The challenge is not the concept, but writing metadata queries that are accurate across schemas and database engines. A reliable approach is to start with INFORMATION_SCHEMA, then switch to engine-specific catalogs only when you need extra detail.

Start with INFORMATION_SCHEMA.COLUMNS

For most SQL engines, the portable baseline is INFORMATION_SCHEMA.COLUMNS. It provides column names, data types, nullability, defaults, and ordinal order.

1SELECT
2  column_name,
3  data_type,
4  is_nullable,
5  column_default,
6  ordinal_position
7FROM information_schema.columns
8WHERE table_schema = 'public'
9  AND table_name = 'orders'
10ORDER BY ordinal_position;

Two details matter:

• Always filter by schema and table.
• Always sort by ordinal_position.

Without schema filtering, you can accidentally read metadata from a similarly named table in another schema.

MySQL and MariaDB Patterns

MySQL supports both quick and detailed options.

Quick interactive view:

SHOW COLUMNS FROM orders;

Script-friendly detailed query:

1SELECT
2  column_name,
3  column_type,
4  is_nullable,
5  column_default,
6  extra,
7  ordinal_position
8FROM information_schema.columns
9WHERE table_schema = DATABASE()
10  AND table_name = 'orders'
11ORDER BY ordinal_position;

Use SHOW COLUMNS for manual inspection and INFORMATION_SCHEMA for automation, reporting, and environment comparison.

PostgreSQL Patterns

PostgreSQL also supports INFORMATION_SCHEMA, and for advanced tooling you can query system catalogs.

Portable version:

1SELECT
2  column_name,
3  data_type,
4  is_nullable,
5  ordinal_position
6FROM information_schema.columns
7WHERE table_schema = 'public'
8  AND table_name = 'orders'
9ORDER BY ordinal_position;

Catalog version with richer type rendering:

1SELECT
2  a.attnum AS ordinal_position,
3  a.attname AS column_name,
4  pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
5  NOT a.attnotnull AS is_nullable
6FROM pg_catalog.pg_attribute a
7JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
8JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
9WHERE n.nspname = 'public'
10  AND c.relname = 'orders'
11  AND a.attnum > 0
12  AND NOT a.attisdropped
13ORDER BY a.attnum;

Use catalogs when you need precise PostgreSQL behavior such as generated columns or internal type formatting.

SQL Server Patterns

SQL Server supports INFORMATION_SCHEMA.COLUMNS and sys.columns.

INFORMATION_SCHEMA option:

1SELECT
2  COLUMN_NAME,
3  DATA_TYPE,
4  IS_NULLABLE,
5  ORDINAL_POSITION
6FROM INFORMATION_SCHEMA.COLUMNS
7WHERE TABLE_SCHEMA = 'dbo'
8  AND TABLE_NAME = 'Orders'
9ORDER BY ORDINAL_POSITION;

System catalog option:

1SELECT
2  c.column_id AS ordinal_position,
3  c.name AS column_name,
4  t.name AS data_type,
5  c.max_length,
6  c.is_nullable
7FROM sys.columns c
8JOIN sys.types t ON t.user_type_id = c.user_type_id
9WHERE c.object_id = OBJECT_ID('dbo.Orders')
10ORDER BY c.column_id;

System catalogs are better when you need engine-specific attributes for migration tooling.

Oracle Pattern

Oracle uses uppercase identifiers by default unless objects were created with quoted names.

1SELECT
2  column_id AS ordinal_position,
3  column_name,
4  data_type,
5  nullable,
6  data_default
7FROM all_tab_columns
8WHERE owner = 'APP_SCHEMA'
9  AND table_name = 'ORDERS'
10ORDER BY column_id;

If you query with lowercase identifiers in Oracle metadata views, you can get empty results even when table exists.

Programmatic Introspection Example

For automation, parameterized queries prevent schema-name injection and keep scripts reusable.

1import psycopg
2
3sql = """
4SELECT column_name, data_type, ordinal_position
5FROM information_schema.columns
6WHERE table_schema = %s
7  AND table_name = %s
8ORDER BY ordinal_position
9"""
10
11with psycopg.connect("dbname=app user=app") as conn:
12    with conn.cursor() as cur:
13        cur.execute(sql, ("public", "orders"))
14        rows = cur.fetchall()
15
16for name, dtype, pos in rows:
17    print(f"{pos}: {name} ({dtype})")

This script runs as-is when database credentials are valid and table exists.

Practical Validation Checklist

When metadata output looks wrong, verify these in order:

1. schema name is correct
2. identifier case matches engine behavior
3. account has metadata permissions
4. query orders by ordinal position
5. table is not a synonym or view when you expected base table

This checklist resolves most false alarms quickly.

Common Pitfalls

A common pitfall is querying by table name alone and getting columns from the wrong schema. Another is assuming case handling is the same in PostgreSQL, MySQL, SQL Server, and Oracle. Teams also forget ORDER BY ordinal_position, producing unstable column ordering in generated documentation. In automation, unparameterized metadata queries create avoidable safety and correctness issues. Finally, developers sometimes jump to engine-specific catalogs prematurely when a portable INFORMATION_SCHEMA query would be clearer and easier to maintain.

Summary

• Use INFORMATION_SCHEMA.COLUMNS first for portable column listing.
• Filter by both schema and table, then sort by ordinal position.
• Use engine-specific catalogs only when you need extra metadata detail.
• Parameterize metadata queries in scripts for reliability and safety.
• Validate schema name, case, and permissions before assuming table metadata is missing.