SQLite PRAGMA index_list and index_info

To inspect a SQLite table’s indexes, you need their names and the columns each one covers. The index_list and index_info PRAGMAs return that metadata: one lists a table’s indexes, and the other returns each index’s key columns.

I’ll show how to inspect a table’s indexes, including their key columns, and explain what an empty result can and cannot tell you.

TL;DR: List SQLite Indexes and Their Columns

Run PRAGMA index_list(‘table_name’) to get the index names, then pass each name to PRAGMA index_info(‘index_name’) to see its key columns.

• index_list reports index metadata for one table.
• index_info reports one row per key column in an index.
• Use index_xinfo when you also need auxiliary index columns.

What Do index_list and index_info Return?

SQLite PRAGMAs are SQLite-specific statements that query or change database settings and metadata. index_list returns one row for each index associated with a table, while index_info returns one row for each key column in a named index.

An index stores key values in an order SQLite can search. Tables can have separate indexes for different queries, and each index can use multiple key columns. I keep the inspections separate because the name and key list answer different questions.

These are read-only inspection commands. They do not create or remove indexes. That makes them a useful first check when you inherit a database or review a migration, since you can compare the schema’s current state with the indexes your application expects.

Each index_list row gives an index name. Pass that exact name to index_info to retrieve its key columns. Compare the inventory with PRAGMA table_info when you also need the table’s columns.

How to List Indexes and Inspect Their Columns

Run index_list first, then inspect each returned index name with index_info. The example below creates an in-memory table and two indexes, so it does not change a database file.

Step 1: Create a small example table

Use this Python script to create a table with a composite index and a unique index, then print each index and its key columns. Python’s built-in sqlite3 module runs the same PRAGMA statements available through SQLite clients.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.executescript("""
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    created_at TEXT,
    status TEXT
);
CREATE INDEX idx_orders_customer_created
    ON orders(customer_id, created_at DESC);
CREATE UNIQUE INDEX idx_orders_status ON orders(status);
""")

for seq, name, unique, origin, partial in connection.execute(
    "PRAGMA index_list('orders')"
):
    print(f"{name}: unique={unique}, origin={origin}, partial={partial}")
    for seqno, cid, column in connection.execute(
        f"PRAGMA index_info('{name}')"
    ):
        print(f"  key {seqno}: cid={cid}, column={column}")

idx_orders_status: unique=1, origin=c, partial=0
  key 0: cid=3, column=status
idx_orders_customer_created: unique=0, origin=c, partial=0
  key 0: cid=1, column=customer_id
  key 1: cid=2, column=created_at

The first row of each group comes from index_list. The indented rows come from index_info. SQLite reports the unique index with unique=1, and its name differs from the composite index.

Step 2: Run index_list for your table

Replace orders with the table name you are inspecting. The result contains seq, name, unique, origin, and partial.

PRAGMA index_list('orders');

(0, 'idx_orders_status', 1, 'c', 0)
(1, 'idx_orders_customer_created', 0, 'c', 0)

This output shows the index name and its metadata in each row. It comes from the executed example above, where the table has a unique status index and a non-unique composite index.

These fields explain why an index exists and which rows it covers.

Field	Meaning
name	The index name to pass to index_info.
unique	1 when the index enforces unique values, otherwise 0.
origin	c for CREATE INDEX, u for a UNIQUE constraint, or pk for a primary-key constraint.
partial	1 when a WHERE clause limits the rows included in the index.
seq	The row’s position in this result, not a stable index identifier.

I use the name field to choose the next command because seq only describes this result’s ordering.

The command takes one table name at a time. For a schema inventory, query the table list and call index_list for each table in your application. SQLite also allows a schema qualifier such as main when attached databases contain similarly named tables.

The partial flag matters when deciding whether an index covers the rows a query needs. A partial index has a WHERE condition in its definition. Its presence does not mean it can serve every query on that table, so compare the index condition with the query predicate.

Step 3: Run index_info for each index

Pass one index name returned by index_list as the argument to index_info. Repeat for each index you want to inspect.

PRAGMA index_info('idx_orders_customer_created');

(0, 1, 'customer_id')
(1, 2, 'created_at')

SQLite returns the key columns in order. Here, customer_id leads the index and created_at follows it. That order is useful when checking how a query’s conditions align with a composite index.

Each result row contains seqno, cid, and name. seqno gives the column’s position within the index. name gives the table column when the index uses a named column.

An expression such as lower(email) has no single table column, so its name can be NULL. That result still means the index has a key expression at that position.

For indexes with multiple key columns, read the rows in seqno order. That order defines the index’s key sequence, so it helps explain whether a query’s filter or sort columns match the index’s leading columns.

The cid field is the column’s rank in the table definition, while name gives its readable identifier. SQLite assigns special values when an index key is an expression instead of a direct column.

For an expression key, rely on seqno to see its position and use index_xinfo for more metadata. The CREATE INDEX text in the schema can identify the expression itself.

Combine both PRAGMAs in a SQL query

SQLite also exposes result-producing PRAGMAs as table-valued functions, so a join can return each index beside its key columns. This form works in SQLite 3.16.0 and later. It is handy when an application needs a compact report rather than running one query per index.

SELECT il.name AS index_name, ii.seqno, ii.name AS column_name
FROM pragma_index_list('orders') AS il
JOIN pragma_index_info(il.name) AS ii
ORDER BY il.name, ii.seqno;

('idx_orders_customer_created', 0, 'customer_id')
('idx_orders_customer_created', 1, 'created_at')

The result repeats the index name for each column. This makes the relationship explicit in one result set, while separate PRAGMA calls are simpler for a quick manual check.

Edge Cases When Inspecting SQLite Indexes

Check the table name and choose the inspection command that matches the detail you need.

Case	What to check
No rows from index_list	Confirm the table name with table_list.
Unexpected index name	Read origin to see whether a constraint created it.
Need auxiliary columns	Use index_xinfo instead of index_info.

An empty result does not identify the cause

When index_list returns no rows, check whether the table exists before concluding that it has no indexes. These checks separate a misspelled table name from an empty index inventory.

PRAGMA table_list;
PRAGMA index_list('orders');

('main', 'orders', 'table', 1, 0, 0)
(0, 'idx_orders_customer', 0, 'c', 0)

An index inventory does not show whether a query uses an index. Inspect the plan for the exact query when usage is the question.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 7;

(2, 0, 56, 'SEARCH orders USING COVERING INDEX idx_orders_customer (customer_id=?)')

The plan names the index selected for this query in the test database. A different dataset or query can produce a different plan. Check the statement you care about instead of treating the index list as proof that every lookup uses the same structure.

Constraint indexes may not have names you chose

SQLite can create indexes to enforce UNIQUE or primary-key constraints. These appear in index_list alongside indexes created explicitly, and the origin field identifies why each one exists. A UNIQUE column constraint creates an automatic index even when the schema has no named CREATE INDEX statement for it.

CREATE TABLE users(email TEXT UNIQUE);
PRAGMA index_list('users');

(0, 'sqlite_autoindex_users_1', 1, 'u', 0)

Use index_xinfo for auxiliary columns

index_info lists key columns only. SQLite indexes can also contain auxiliary columns used to locate the table row.

Run index_xinfo when you need auxiliary entries, descending flags, or collation details. I choose it for a full index view, while index_info is the shorter answer when key columns are enough.

PRAGMA index_xinfo('idx_orders_customer_created');

(0, 1, 'customer_id', 0, 'BINARY', 1)
(1, 2, 'created_at', 1, 'BINARY', 1)
(2, -1, None, 0, 'BINARY', 0)

The final row has key=0, which marks it as an auxiliary column rather than a key column. The desc value also shows descending order for created_at. This fuller output answers questions that index_info does not cover.

There is a documented exception to keep in mind: if the requested name is a WITHOUT ROWID table rather than an index, index_info can return that table’s primary-key columns. Check the object name when the output looks like a key list you did not expect. This behavior is documented for SQLite 3.30.0 and later.

For broader query tuning after you have inspected the existing indexes, see the SQLite performance checklist. Keep the inspection step separate from creating or removing indexes, which can affect write performance and constraints.

Conclusion: Inspect Before Adding an Index

index_list gives you the indexes attached to a table, and index_info shows the key columns in each one. Use index_xinfo when key columns alone are not enough, then check query plans before deciding whether an index needs to change.

SQLite’s PRAGMA documentation lists the full output definitions and additional schema-qualified forms.

FAQ

These answers cover two common follow-up questions about SQLite index inspection.