List Indexes in SQL: MySQL, MariaDB, PostgreSQL & More

Each database exposes index metadata differently. MySQL and MariaDB use SHOW INDEX; PostgreSQL has pg_indexes; SQL Server uses catalog views; SQLite provides PRAGMA; and Oracle exposes index views. These methods show definitions and indexed columns, but the details and visibility rules differ.

Quick reference

MySQL and MariaDB

Use SHOW INDEX to list a table’s indexes and their indexed columns:

SHOW INDEX FROM sakila.actor;

The Key_name identifies the index; Seq_in_index gives each key column’s position. Non_unique = 0 indicates a unique index. To include the database name, use SHOW INDEX FROM sakila.actor even when a different database is selected.

For functional key parts in MySQL 8.0.13 and later, SHOW INDEX reports the expression in the Expression field and sets Column_name to NULL. See the MySQL SHOW INDEX reference.

For MariaDB syntax and output, see its SHOW INDEX reference. For MySQL examples, see SQLiz’s MySQL index listing guide.

PostgreSQL

Query pg_indexes to list indexes in a schema, or to view their reconstructed definitions:

SELECT indexname, tablename, indexdef
FROM pg_catalog.pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'orders'
ORDER BY indexname;

The view reports the schema, table, index name, tablespace, and CREATE INDEX definition. See PostgreSQL’s pg_indexes reference and SQLiz’s PostgreSQL index listing guide.

SQL Server

Join sys.indexes to sys.index_columns and sys.columns to list index columns and distinguish key columns from included columns:

SELECT
    i.name AS index_name,
    i.type_desc AS index_type,
    i.is_unique,
    i.has_filter,
    i.filter_definition,
    c.name AS column_name,
    ic.key_ordinal,
    ic.is_included_column
FROM sys.indexes AS i
JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
   AND ic.index_id = i.index_id
JOIN sys.columns AS c
    ON c.object_id = ic.object_id
   AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID(N'dbo.Orders')
  AND i.index_id > 0
ORDER BY i.name,
    CASE
        WHEN ic.key_ordinal > 0 THEN 0
        WHEN ic.is_included_column = 1 THEN 1
        ELSE 2
    END,
    ic.key_ordinal,
    ic.index_column_id;

This returns one row per index column. key_ordinal = 0 indicates a column that isn’t part of the ordered key; is_included_column = 1 marks an included column. has_filter indicates a filtered index, and filter_definition contains its predicate; that value can be NULL for an unfiltered index or when you lack permission to view the metadata. Visibility is limited to objects the current user owns or can access. See Microsoft’s sys.indexes and sys.index_columns references.

SQLite

Use PRAGMA index_list to see the indexes associated with a table:

PRAGMA index_list('orders');

Use PRAGMA index_info to see the columns in an index:

PRAGMA index_info('idx_orders_created_at');

index_info lists key columns, but an expression index appears with cid = -2 and a NULL column name; inspect the CREATE INDEX statement in sqlite_schema to see the expression. Use PRAGMA index_xinfo when you also need auxiliary index columns. The index_list result includes the index name, uniqueness flag, origin, and partial-index flag. See SQLite’s index_list, index_info, and index_xinfo documentation.

For expression text, auxiliary columns, and table-valued pragma examples, see SQLiz’s SQLite index metadata guide.

Oracle

Query USER_INDEXES for indexes owned by the current user. Join USER_IND_COLUMNS when you need the indexed columns and their positions:

SELECT
    i.index_name,
    i.uniqueness,
    c.column_name,
    c.column_position
FROM user_indexes i
JOIN user_ind_columns c
    ON c.index_name = i.index_name
   AND c.table_name = i.table_name
WHERE i.table_name = 'ORDERS'
ORDER BY i.index_name, c.column_position;

For indexes in other schemas, use ALL_INDEXES and ALL_IND_COLUMNS for objects accessible to your account; DBA_INDEXES requires administrative privileges. With unquoted Oracle identifiers, dictionary names are stored in uppercase. See Oracle’s USER_INDEXES, USER_IND_COLUMNS, and index administration guide.

For function-based indexes, query USER_IND_EXPRESSIONS to see each indexed expression and its position:

SELECT index_name, column_position, column_expression
FROM user_ind_expressions
WHERE table_name = 'ORDERS'
ORDER BY index_name, column_position;

See Oracle’s USER_IND_EXPRESSIONS reference.

This guide lists index metadata; it doesn’t determine whether a query uses an index or whether an index improves performance. For index design, see the database-specific index guides in the SQLiz MySQL and PostgreSQL references.