Introspection

Describe a table — show columns, types, and constraints

Q: How do I describe a table in SQL?

Portable: SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'orders'. Engine shortcuts: \d orders (psql, also shows indexes/FKs), DESCRIBE orders (MySQL / Snowflake / Hive), sp_help 'orders' (SQL Server), PRAGMA table_info('orders') (SQLite).

Q: How do I see the original CREATE TABLE statement?

MySQL: SHOW CREATE TABLE orders. SQLite: .schema orders in CLI, or SELECT sql FROM sqlite_master WHERE name = 'orders'. Postgres has no built-in for this — use pg_dump --schema-only --table=orders db_name. SQL Server: right-click in SSMS → Script Table As → CREATE.

Q: Why is is_nullable returned as YES/NO instead of TRUE/FALSE?

A quirk of the SQL standard: information_schema.columns.is_nullable is a string column with values 'YES' and 'NO'. Cast or compare as string: WHERE is_nullable = 'YES'. Some engines also expose a boolean equivalent in their native catalog (e.g., pg_attribute.attnotnull).

Q: How do I see foreign keys for a table?

See the dedicated Find foreign keys page. Joining information_schema.referential_constraints with key_column_usage returns the full FK list. On Postgres, \d orders in psql shows them inline.

Q: Can I run DESCRIBE programmatically?

On most engines, no: DESCRIBE is interactive only. Use the INFORMATION_SCHEMA query instead, which returns proper rows you can parse from any client. Some engines (Snowflake) support DESC TABLE programmatically; check before relying on it.

Short answer: SELECT * FROM information_schema.columns WHERE table_name = 'orders' works on every major engine. Most engines also have a meta-command that's faster interactively: \d orders (psql), DESCRIBE orders (MySQL / Snowflake / Hive), sp_help orders (SQL Server).

Canonical SQL #

sql

-- Portable: works on every engine that supports INFORMATION_SCHEMA.
SELECT
  column_name,
  data_type,
  character_maximum_length,
  is_nullable,
  column_default,
  ordinal_position
FROM   information_schema.columns
WHERE  table_name = 'orders'
ORDER  BY ordinal_position;

-- Engine-specific shortcuts.
\d orders                                -- psql (Postgres) — also shows indexes, FKs, defaults
DESCRIBE orders;                          -- MySQL / Snowflake / Hive / Databricks
DESC orders;                              -- shorthand on most engines that accept DESCRIBE
sp_help 'dbo.orders';                     -- SQL Server
PRAGMA table_info('orders');              -- SQLite

Example #

Loading SQL editor...

Common variants #

sql

-- Find columns of a specific type across the database (Postgres).
SELECT table_name, column_name
FROM   information_schema.columns
WHERE  data_type = 'jsonb'
  AND  table_schema = 'public';

-- Find all nullable columns in a table.
SELECT column_name
FROM   information_schema.columns
WHERE  table_name = 'users'
  AND  is_nullable = 'YES';

-- Find columns named like "email" anywhere.
SELECT table_schema, table_name, column_name
FROM   information_schema.columns
WHERE  column_name ILIKE '%email%'
  AND  table_schema NOT IN ('pg_catalog', 'information_schema');

-- Postgres: get the CREATE TABLE statement (no standard, but useful).
-- pg_dump --schema-only --table=orders my_db
-- ...or use pg_get_tabledef from extensions.

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL	`\d orders` (psql)	meta-command only; use `information_schema.columns` in code
MySQL	`DESCRIBE orders`	`SHOW CREATE TABLE` returns the full DDL
SQL Server	`sp_help 'dbo.orders'`	—
BigQuery	`project.dataset.INFORMATION_SCHEMA.COLUMNS`	must qualify with project + dataset
Snowflake	`DESC TABLE orders`	`information_schema.columns` is per-database
Oracle	`DESC orders` (SQL*Plus)	catalogs: `USER_TAB_COLUMNS` / `ALL_TAB_COLUMNS`
SQLite	`PRAGMA table_info('orders')`	no `information_schema`
DuckDB	`DESCRIBE orders`	`PRAGMA table_info` and `information_schema.columns` also work

Dialect notes #

Postgres: \d orders is the gold standard: columns, indexes, FKs, defaults, triggers, all on one screen. \d+ adds storage, stats, and descriptions.
MySQL / MariaDB: DESCRIBE table (or DESC). SHOW CREATE TABLE table returns the original CREATE TABLE DDL — extremely useful.
SQL Server: sp_help 'schema.table' returns columns plus constraints. sys.columns joined with sys.types gives more control.
SQLite: PRAGMA table_info('orders') returns columns. .schema orders in CLI shows CREATE TABLE DDL.
Oracle: DESC schema.table in SQL*Plus. USER_TAB_COLUMNS, ALL_TAB_COLUMNS, DBA_TAB_COLUMNS for the catalogs.
Snowflake: DESC TABLE my_table or SHOW COLUMNS IN TABLE my_table. information_schema.columns is per-database.
BigQuery: SELECT * FROM \project.dataset\.INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'orders' — qualify by project + dataset. bq show dataset.orders from CLI.
DuckDB: DESCRIBE orders, PRAGMA table_info, or information_schema.columns all work.
Redshift: SVV_COLUMNS (cluster-aware) or information_schema.columns (Postgres-compatible).

FAQ #

How do I describe a table in SQL?

Portable: SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'orders'. Engine shortcuts: \d orders (psql, also shows indexes/FKs), DESCRIBE orders (MySQL / Snowflake / Hive), sp_help 'orders' (SQL Server), PRAGMA table_info('orders') (SQLite).

How do I see the original CREATE TABLE statement?

MySQL: SHOW CREATE TABLE orders. SQLite: .schema orders in CLI, or SELECT sql FROM sqlite_master WHERE name = 'orders'. Postgres has no built-in for this — use pg_dump --schema-only --table=orders db_name. SQL Server: right-click in SSMS → Script Table As → CREATE.

Why is is_nullable returned as YES/NO instead of TRUE/FALSE?

A quirk of the SQL standard: information_schema.columns.is_nullable is a string column with values 'YES' and 'NO'. Cast or compare as string: WHERE is_nullable = 'YES'. Some engines also expose a boolean equivalent in their native catalog (e.g., pg_attribute.attnotnull).

How do I find columns of a specific type across all tables?

SELECT table_name, column_name FROM information_schema.columns WHERE data_type = 'jsonb' AND table_schema = 'public'. Useful for migration audits, type cleanups, or finding "where do we store JSON anyway?"

How do I see foreign keys for a table?

See the dedicated Find foreign keys page. Joining information_schema.referential_constraints with key_column_usage returns the full FK list. On Postgres, \d orders in psql shows them inline.

Can I run DESCRIBE programmatically?

On most engines, no: DESCRIBE is interactive only. Use the INFORMATION_SCHEMA query instead, which returns proper rows you can parse from any client. Some engines (Snowflake) support DESC TABLE programmatically; check before relying on it.

Tip

Save the column-discovery query as a snippet in your SQL editor. A typical day involves looking up "what columns does this table have, and what's nullable?" 5-10 times. The query is identical every time except for the table name. Most editors (DBeaver, DataGrip, VS Code SQLTools) have a snippet feature — cols → SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = '$1' saves real minutes per week.