List Table Columns in SQL: MySQL, MariaDB, PostgreSQL & More

To inspect a table’s columns, use each database’s metadata command or catalog. MySQL and MariaDB provide SHOW COLUMNS; psql has the \d shortcut; SQL Server has catalog views; SQLite uses PRAGMA; and Oracle exposes data dictionary views. Some commands are client shortcuts rather than SQL, and each database limits results according to its own schema and permission rules.

Quick reference

MySQL and MariaDB

Use SHOW COLUMNS or its DESCRIBE shortcut. Specify the database when the table isn’t in the selected database:

SHOW COLUMNS FROM sakila.actor;

The result includes the field name, type, whether NULL is allowed, key information, default value, and extra attributes such as auto_increment. Add FULL to show additional details such as collation and comments, or add LIKE to filter column names:

SHOW FULL COLUMNS FROM sakila.actor LIKE 'a%';

See the MySQL SHOW COLUMNS and MariaDB SHOW COLUMNS references. For MySQL examples, see SHOW COLUMNS.

PostgreSQL

In psql, use \d to display a relation’s columns and types; add + for extra details such as comments:

\d+ public.orders

This is a psql meta-command, not SQL. To query the SQL-standard metadata view, filter by schema and table name:

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

See PostgreSQL’s psql reference and information_schema.columns documentation. For more examples, see PostgreSQL table descriptions.

SQL Server

Query the catalog views when you need SQL Server column metadata. This example lists the column order, name, SQL data type, and nullability for one table:

SELECT
    c.column_id,
    c.name AS column_name,
    TYPE_NAME(c.user_type_id) AS data_type,
    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
WHERE s.name = N'dbo'
  AND t.name = N'Orders'
ORDER BY c.column_id;

Replace dbo and Orders with the table’s schema and name. SQL Server’s catalog metadata visibility is limited to objects the account owns or can access. See Microsoft’s sys.columns and metadata visibility references.

SQLite

Use PRAGMA table_xinfo to inspect columns, including generated or hidden columns:

PRAGMA main.table_xinfo('orders');

PRAGMA table_info is a shorter alternative, but it omits generated and hidden columns. To return metadata in a queryable rowset, SQLite 3.16.0 and later also support the table-valued form:

SELECT cid, name, type, "notnull", dflt_value, pk, hidden
FROM pragma_table_xinfo('orders')
ORDER BY cid;

See SQLite’s PRAGMA documentation.

Oracle

Query USER_TAB_COLUMNS for columns of tables, views, and clusters owned by the current user:

SELECT column_id, column_name, data_type, nullable
FROM user_tab_columns
WHERE table_name = 'ORDERS'
ORDER BY column_id;

For unquoted Oracle identifiers, dictionary names are stored in uppercase. ALL_TAB_COLUMNS covers objects accessible to the current user across schemas and includes an OWNER column; filter that column to inspect one schema. See Oracle’s USER_TAB_COLUMNS and ALL_TAB_COLUMNS references.

For the preceding step—finding the table name—see how to list tables across SQL databases. Check the connected database, schema, and account privileges if an expected table or column doesn’t appear.