SQLite PRAGMA index_list: List Indexes and Columns

SQLite’s index pragmas let you list the indexes associated with a table, then inspect the columns and properties of each index. They report index definitions; to check whether a query uses an index, inspect its plan with EXPLAIN QUERY PLAN. That output is for interactive debugging, and its format can change between SQLite releases.

PRAGMA commands are SQLite-specific. SQLite silently ignores unknown PRAGMA names, so check spelling if a mistyped command produces no expected result.

List a table’s indexes

Use PRAGMA index_list to list the indexes associated with a table in the main database:

PRAGMA main.index_list('orders');

The result has one row per index:

For a database attached as archive, qualify the pragma with the schema name, such as PRAGMA archive.index_list('orders');.

Inspect key columns

Use PRAGMA index_info with an index name returned by index_list:

PRAGMA main.index_info('idx_orders_created_at');

It returns one row per key column: seqno is the column’s position in the index, cid is its column ID in the table, and name is the column name. Expression-index terms have cid = -2 and name = NULL; the special rowid key has cid = -1 and name = NULL.

PRAGMA index_info shows key columns only. Use PRAGMA index_xinfo when you also need auxiliary index columns and details such as sort direction and collation:

PRAGMA main.index_xinfo('idx_orders_created_at');

The key output column is 1 for key columns and 0 for auxiliary columns. For an expression index, cid is -2 and name is NULL here as well.

Read the index definition

To see the CREATE INDEX statement, query sqlite_schema:

SELECT name, sql
FROM sqlite_schema
WHERE type = 'index'
  AND tbl_name = 'orders'
ORDER BY name;

This shows expression text and a partial index’s WHERE predicate. The sql column is NULL for indexes created automatically to enforce PRIMARY KEY or UNIQUE constraints.

Query index metadata as rows

SQLite 3.16.0 and later expose result-producing pragmas as table-valued functions. You can join pragma_index_list to pragma_index_info to return a row per index key column:

SELECT
    il.name AS index_name,
    il."unique" AS is_unique,
    il.origin,
    il.partial,
    ii.seqno,
    ii.cid,
    ii.name AS column_name
FROM pragma_index_list('orders') AS il,
     pragma_index_info(il.name) AS ii
ORDER BY il.name, ii.seqno;

For an attached database, pass its schema name as the final argument to each table-valued pragma. For example, to list the indexes on archive.orders:

SELECT *
FROM pragma_index_list('orders', 'archive');

SQLite documents the schema argument as optional for table-valued PRAGMAs; when omitted, the main database is used.

For index metadata across MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, and SQLite, see list indexes by database. For the command to find SQLite tables and views, see list tables by database.

See SQLite’s official index_list, index_info, index_xinfo, and sqlite_schema references.