Sql Query To List Columns In A Table

7 min read

Understanding how to inspect the structure of a table is one of the most fundamental skills in database work. Whether you are debugging a query, documenting a legacy schema, or onboarding onto a new project, knowing the right SQL query to list columns in a table saves time and prevents costly mistakes. Different database engines expose this metadata in different ways, and mastering each approach gives you full visibility into your data model.

Why You Need to List Columns in a Table

Before writing complex joins or migrations, you often need a quick answer to a simple question: what columns exist in this table? Listing columns helps you:

  • Verify schema changes after running migrations or deployments.
  • Explore unfamiliar databases when documentation is missing or outdated.
  • Build dynamic SQL that references column names programmatically.
  • Audit data types and constraints before inserting or transforming data.
  • Debug errors caused by misspelled or missing column names.

Each major database system provides its own catalog tables and views for this purpose. Below are the most reliable queries for the most popular engines The details matter here..

Listing Columns in MySQL and MariaDB

MySQL offers two primary approaches: the SHOW COLUMNS command and the INFORMATION_SCHEMA database.

Using SHOW COLUMNS

The simplest method is the SHOW COLUMNS statement:

SHOW COLUMNS FROM employees;

This returns the field name, type, nullability, key information, default value, and extra attributes. You can also use the DESCRIBE shortcut:

DESCRIBE employees;

Both commands are fast and easy to remember, making them ideal for interactive sessions.

Using INFORMATION_SCHEMA

For programmatic access or when you need to filter results, query the INFORMATION_SCHEMA.COLUMNS view:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'employees'
ORDER BY ORDINAL_POSITION;

This approach is powerful because you can join it with other metadata views, filter by data type, or generate reports across many tables at once.

Listing Columns in PostgreSQL

PostgreSQL provides the information_schema as well as system catalogs like pg_catalog. The standard-compliant approach uses information_schema.columns:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'employees'
ORDER BY ordinal_position;

For richer detail, including column comments and storage information, query the system catalog directly:

SELECT
  a.attname AS column_name,
  format_type(a.atttypid, a.atttypmod) AS data_type,
  a.attnotnull AS not_null,
  pgd.description AS comment
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_description pgd
  ON pgd.objoid = c.oid AND pgd.objsubid = a.attnum
WHERE c.relname = 'employees'
  AND n.nspname = 'public'
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

The psql command-line tool also supports a convenient shortcut: \d employees And that's really what it comes down to..

Listing Columns in SQL Server

SQL Server stores metadata in system views such as sys.On top of that, columns and sys. types.

SELECT
  c.name AS column_name,
  t.name AS data_type,
  c.max_length,
  c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.employees')
ORDER BY c.column_id;

Alternatively, the INFORMATION_SCHEMA.COLUMNS view works across most relational systems:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'employees';

For a quick look in SQL Server Management Studio, you can also expand the table node in Object Explorer or run sp_help 'employees'.

Listing Columns in Oracle

Oracle provides the USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS views. The right one depends on your privileges:

SELECT column_name, data_type, nullable, data_default
FROM USER_TAB_COLUMNS
WHERE table_name = 'EMPLOYEES'
ORDER BY column_id;

Use ALL_TAB_COLUMNS to see tables you have access to but do not own, and DBA_TAB_COLUMNS if you have administrative privileges. Note that Oracle stores unquoted identifiers in uppercase, so the table name must match exactly.

Listing Columns in SQLite

SQLite offers a pragmatic command through its command-line interface:

PRAGMA table_info(employees);

This returns the column ID, name, type, nullability, default value, and primary key flag. You can also query PRAGMA table_xinfo(employees) to include hidden columns generated by virtual tables It's one of those things that adds up..

A Portable Query Using INFORMATION_SCHEMA

If you work across multiple databases and want a single query that works almost everywhere, the INFORMATION_SCHEMA.COLUMNS view is your best bet. It is supported by MySQL, PostgreSQL, SQL Server, and several other engines:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'employees'
ORDER BY ORDINAL_POSITION;

Keep in mind that schema and case-sensitivity rules vary between engines, so you may need to adjust the WHERE clause for each platform.

Practical Tips for Working With Column Metadata

  • Always filter by schema. Tables with the same name can exist in multiple schemas, and unfiltered queries may return confusing results.
  • Order by ordinal position. This preserves the physical column order, which is useful when comparing schemas or generating documentation.
  • Check case sensitivity. PostgreSQL folds unquoted identifiers to lowercase, while Oracle folds them to uppercase.
  • Include data types and nullability. Column names alone rarely tell the full story; types and constraints matter for safe queries.
  • Automate schema documentation. You can export column metadata to CSV or Markdown to keep documentation in sync with the database.

Frequently Asked Questions

What is the fastest way to list columns in a table?

In MySQL and MariaDB, SHOW COLUMNS FROM table_name is the quickest. In PostgreSQL, \d table_name in psql is fastest. Think about it: for programmatic use, query INFORMATION_SCHEMA. COLUMNS.

Can I list columns for all tables at once?

Yes. Remove the TABLE_NAME filter and group or order by table name:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Why does my query return no rows?

The most common causes are a missing schema filter, incorrect letter case, or insufficient privileges. Double-check the exact table name and the schema it belongs to That's the part that actually makes a difference..

How do I see column comments?

In PostgreSQL, join pg_description. COLUMN_COMMENT. COLUMNS.On the flip side, in MySQL, query INFORMATION_SCHEMA. In SQL Server, use sys.extended_properties.

Conclusion

Knowing the right SQL query to list columns in a table is a small skill with a big payoff. MySQL and MariaDB favor SHOW COLUMNS, PostgreSQL and SQL Server reward you for learning their system catalogs, Oracle relies on USER_TAB_COLUMNS,

and SQLite keeps it simple with PRAGMA table_info. The ANSI-standard INFORMATION_SCHEMA.COLUMNS view remains the most portable option for cross-platform scripts and tools.

Mastering these commands turns a routine inspection task into a reliable building block for automation, migration scripts, and dynamic SQL generation. Whether you are debugging a production issue, onboarding to a new codebase, or building a data catalog, the ability to instantly retrieve accurate column metadata—including data types, nullability, and default values—saves hours of guesswork Took long enough..

Pick the syntax native to your primary platform for daily speed, but keep the INFORMATION_SCHEMA pattern in your toolkit for any code that needs to travel across database engines. With these queries at your fingertips, you’ll never again waste time wondering “what columns does this table actually have?”

Worth pausing on this one Took long enough..

Quick Reference Cheat Sheet

Database Native Command (CLI / SQL) Standard View Query Key System Catalog Alternative
MySQL / MariaDB SHOW COLUMNS FROM tbl; <br> DESCRIBE tbl; SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl'; sys.So columns <br> pg_attribute / pg_class (for internals)
SQL Server sp_columns 'tbl' <br> sp_help 'tbl' SELECT * FROM INFORMATION_SCHEMA. columns + sys.COLUMNS WHERE TABLE_NAME = 'tbl'; SELECT * FROM information_schema.Even so, cOLUMNS WHERE TABLE_NAME = 'tbl' AND TABLE_SCHEMA = DATABASE();
PostgreSQL \d tbl (psql meta-command) SELECT * FROM INFORMATION_SCHEMA. tables
Oracle DESC tbl (SQL*Plus / SQLcl) SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TBL'; SHOW COLUMNS IN TABLE tbl;
BigQuery N/A (UI / API) SELECT * FROM project.schema tbl` (CLI) Not fully supported
Snowflake DESC TABLE tbl; SELECT * FROM INFORMATION_SCHEMA.On top of that, dataset. Think about it: cOLUMNS WHERE TABLE_NAME = 'TBL'; USER_TAB_COLUMNS <br> ALL_TAB_COLUMNS <br> DBA_TAB_COLUMNS
SQLite . types + `sys.INFORMATION_SCHEMA.

Advanced: Getting Rich Metadata in One Shot

For documentation generators or schema diff tools, you often need more than names and types. Here are “batteries-included” queries for the major engines that return ordinal position, data type, max length, precision/scale, nullability, default value, identity/auto-increment status, and comments in a single result set.

PostgreSQL (Rich Catalog Query)

SELECT 
    a.attnum AS ordinal_position,
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
    a.attnotnull AS not_null,
    pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
    CASE WHEN a.attidentity IN ('a', 'd') THEN 'YES' ELSE 'NO' END AS is_identity,
    col_description(a.attrelid, a.attnum) AS column_comment
FROM pg_attribute a
LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
WHERE a.attrelid = 'your_schema.your_table'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

SQL Server (Rich Catalog Query)

SELECT 
    c.column_id AS ordinal_position,
    c.name AS column_name,
    t.name AS data_type,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    dc.definition AS column_default,
    c.is_identity,
    ep.value AS column_comment
FROM sys.columns c
JOIN sys.types t ON
Don't Stop

Out This Week

Dig Deeper Here

Covering Similar Ground

Thank you for reading about Sql Query To List Columns In A Table. 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