Every so often I inherit a SQLite database I didn’t build myself, and one of the very first things I want to know is: what indexes already exist on this table? Rather than digging through old migration scripts or guessing, SQLite gives me a direct, reliable way to ask the database itself. That tool is the index_list PRAGMA, and in this article I want to explain exactly what it does, how to use it, how to interpret its output, and where it fits into your broader workflow of inspecting and optimizing a SQLite database.
What Is a PRAGMA in SQLite?
Before getting into index_list specifically, it helps to understand what a PRAGMA actually is. PRAGMAs are special SQLite-specific commands that let you query or modify internal operational parameters of the SQLite library and database connection. They’re not standard SQL — you won’t find PRAGMA in most other database systems in this exact form — but they’re an essential part of working effectively with SQLite.
Some PRAGMAs change behavior (like PRAGMA foreign_keys = ON;, which enables foreign key enforcement). Others are purely informational, letting you inspect the internal structure or state of your database. index_list falls into this second category — it’s a read-only, informational PRAGMA that reports which indexes exist on a given table.
What Does index_list Do?
PRAGMA index_list(table_name); returns a list of all indexes associated with the specified table, including indexes created explicitly with CREATE INDEX, as well as implicit indexes that SQLite automatically creates to enforce UNIQUE and PRIMARY KEY constraints.
This is incredibly useful for a few common scenarios:
- Auditing an unfamiliar database to understand its existing indexing strategy.
- Debugging slow queries by first confirming whether an expected index actually exists.
- Verifying that a migration script correctly created the indexes it was supposed to.
- Building internal tooling or admin dashboards that need to introspect schema details programmatically.
Basic Syntax
PRAGMA index_list(table_name);
You can also use the function-call-style syntax:
PRAGMA index_list('table_name');
Both forms work identically in SQLite. I tend to use the parenthesis-with-quotes form out of habit, since it more closely resembles how I’d pass a string argument in most other contexts, but either is perfectly valid.
A Practical Example
Let’s set up a sample table with a few different kinds of indexing:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
email TEXT UNIQUE,
department TEXT,
last_name TEXT
);
CREATE INDEX idx_employees_department ON employees(department);
CREATE INDEX idx_employees_last_name ON employees(last_name);
Now, running:
PRAGMA index_list(employees);
Returns a result set that looks something like this:
| seq | name | unique | origin | partial |
|---|---|---|---|---|
| 0 | idx_employees_last_name | 0 | c | 0 |
| 1 | idx_employees_department | 0 | c | 0 |
| 2 | sqlite_autoindex_employees_1 | 1 | u | 0 |
Let’s walk through exactly what each column means, because this is where a lot of the real value of index_list lives.
Understanding the Output Columns
seq
This is simply a sequential number identifying the position of each index within the returned list. It’s mostly useful for ordering purposes and doesn’t carry any deeper structural meaning about the index itself.
name
This is the name of the index. For indexes you created explicitly with CREATE INDEX idx_name ON table(column);, this will be the exact name you chose. For indexes SQLite creates automatically to enforce a UNIQUE or PRIMARY KEY constraint (when that constraint isn’t already handled by the table’s rowid), you’ll see an auto-generated name following the pattern sqlite_autoindex_<table_name>_<n>.
In the example above, sqlite_autoindex_employees_1 was created automatically because we declared email TEXT UNIQUE — SQLite needed an index to actually enforce that uniqueness constraint efficiently, so it created one behind the scenes without us explicitly asking for it.
unique
This column tells you whether the index enforces uniqueness. A value of 1 means yes, this index guarantees that no two rows can share the same indexed value(s) — this is the case for indexes backing UNIQUE or PRIMARY KEY constraints. A value of 0 means it’s a regular, non-unique index, purely there to speed up lookups, joins, or sorting, without restricting what values can be stored.
origin
This column tells you why the index exists — essentially, what created it. There are three possible values:
c— the index was created explicitly by aCREATE INDEXstatement.u— the index was automatically created by SQLite to enforce aUNIQUEconstraint.pk— the index was automatically created by SQLite to enforce aPRIMARY KEYconstraint (this typically only appears for composite primary keys or primary keys on non-INTEGERcolumns, since a simpleINTEGER PRIMARY KEYis usually implemented directly via the table’srowidrather than a separate index).
This column is genuinely valuable when auditing a database, because it lets you immediately distinguish “indexes someone deliberately added for performance reasons” from “indexes SQLite quietly generated to enforce a constraint.” I’ve seen developers get confused looking at a list of indexes, wondering why there are more indexes than they remember creating — origin answers that question immediately.
partial
This column indicates whether the index is a partial index — an index that only covers a subset of rows in the table, based on a WHERE clause included in the original CREATE INDEX statement. A value of 1 means it’s partial; 0 means it covers the entire table.
For example:
CREATE INDEX idx_active_users ON users(email) WHERE is_active = 1;
Running PRAGMA index_list(users); afterward would show partial = 1 for idx_active_users, telling you this index only includes rows where is_active = 1, rather than indexing every row in the table. Partial indexes are a great space and performance optimization when you frequently query a specific, well-defined subset of a large table.
Combining index_list With index_info
index_list tells you which indexes exist on a table, but it doesn’t tell you which columns each index actually covers, or in what order. For that, you need to follow up with the related index_info PRAGMA (which I cover in detail in a separate article), passing in each index name you got back from index_list.
PRAGMA index_list(employees);
-- Returns index names, then for each one:
PRAGMA index_info(idx_employees_department);
This two-step workflow — first listing indexes on a table, then inspecting the details of each one — is the standard pattern for fully understanding a table’s indexing structure through PRAGMAs alone, without needing to dig through raw schema SQL.
Comparing index_list to Querying sqlite_master
You might wonder whether you could get similar information just by querying sqlite_master directly:
SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = 'employees';
This does work, and it has one advantage: it shows you the exact original CREATE INDEX SQL statement, which index_list does not. However, sqlite_master only shows indexes that were created explicitly via CREATE INDEX — it does not include the automatically generated indexes that back UNIQUE or PRIMARY KEY constraints, since those don’t have their own explicit sql entry in sqlite_master in the same way (they show up with a NULL sql column, in fact, if you look closely).
index_list, by contrast, gives you the complete picture — every index affecting the table, both explicit and implicit — along with structured metadata like unique and origin that would otherwise require manual interpretation of raw SQL text. I generally recommend using index_list as your primary tool for structured, programmatic inspection, and falling back to sqlite_master only when you specifically need to see the original CREATE INDEX statement’s exact syntax.
Practical Use Case: Auditing Before a Performance Review
Let’s say I’ve just been handed a slow-running application backed by a SQLite database, and I want to quickly understand what indexing already exists across a handful of key tables before I start optimizing.
PRAGMA index_list(orders);
PRAGMA index_list(order_items);
PRAGMA index_list(customers);
Running these three queries gives me an immediate inventory. If I see that order_items has no index at all on its order_id foreign key column (which I’d confirm by following up with index_info on each returned index), that’s an immediate red flag — foreign key columns used heavily in joins almost always benefit from an index, and its absence is often exactly why a query involving order_items is running slowly.
Practical Use Case: Verifying a Migration
After running a schema migration script that’s supposed to add several new indexes, I like to verify the result directly rather than just trusting that the script ran without errors.
PRAGMA index_list(products);
If the expected new index name doesn’t appear in the output, I know immediately that something in the migration didn’t apply correctly, before I ever get to the point of running a slow query in production and wondering why.
Using index_list From the Command-Line Shell
If you’re working directly in the sqlite3 command-line shell rather than through application code, there’s also a simpler built-in shortcut that wraps similar information:
.indexes employees
This dot-command lists the index names for a given table, though it doesn’t give you the same structured detail (unique, origin, partial) that PRAGMA index_list provides. For quick manual exploration, .indexes is convenient; for anything programmatic, or when you need the full metadata, PRAGMA index_list is the better tool.
Common Pitfalls
Forgetting that unique constraints create hidden indexes. If you’re counting how many indexes you’ve “created,” remember that UNIQUE and certain PRIMARY KEY declarations silently add their own indexes that you didn’t explicitly write with CREATE INDEX. index_list‘s origin column is exactly how you catch this.
Assuming index_list tells you which columns are indexed. It doesn’t — you need index_info for that. index_list only tells you that an index exists, its name, and some high-level metadata about it.
Querying a table name that doesn’t exist. PRAGMA index_list on a nonexistent table simply returns an empty result set rather than throwing an explicit error, which can be slightly misleading if you’ve made a typo in the table name — always double check your spelling if you get back nothing when you expected results.
Best Practices
- Use
index_listas your starting point whenever auditing or documenting a table’s indexing strategy. - Follow up with
index_infoto get the actual column-level detail for each index found. - Pay attention to the
origincolumn to distinguish deliberate performance indexes from constraint-enforcement indexes. - Check
partialbefore assuming an index covers your entire query’sWHEREcondition — a partial index might not apply to the specific rows your query is filtering. - Combine with
sqlite_masterwhen you specifically need to see the originalCREATE INDEXSQL text. - Run this PRAGMA as a standard step in any performance investigation, before writing new indexes blindly — you might find the index you need already exists, just under a different name than you expected.
Using index_list Through Application Code
If you’re building tooling around SQLite — an admin dashboard, a schema documentation generator, or a database health-check script — you’ll typically run index_list through whatever database driver your application uses, rather than manually through the shell. The mechanics are the same regardless of language: you execute the PRAGMA as if it were a query and iterate over the returned rows.
For example, in Python using the built-in sqlite3 module:
import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute("PRAGMA index_list(employees);")
for row in cursor.fetchall():
seq, name, unique, origin, partial = row
print(f"Index: {name}, Unique: {bool(unique)}, Origin: {origin}, Partial: {bool(partial)}")
This kind of script is genuinely useful as the foundation for an automated schema audit tool — something that runs periodically across all your tables and flags anything unexpected, like a table with zero non-constraint indexes despite having foreign key columns that are frequently queried.
Building a Full Table Index Report
Since index_list only accepts one table name at a time, if you want a complete picture across your entire database, you first need to enumerate all table names (typically from sqlite_master or PRAGMA table_list), then loop through each one calling index_list.
SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%';
Then, for each table name returned, you’d run PRAGMA index_list(table_name); in turn. This two-step process — first discovering tables, then discovering indexes per table — is the standard pattern for building any kind of comprehensive schema inspection tool on top of SQLite’s PRAGMA interface, since PRAGMAs generally operate on one object (a table, an index) at a time rather than offering a single database-wide summary in one call.
index_list and Schema Documentation
I’ve found index_list, combined with index_info, genuinely valuable for keeping schema documentation honest and up to date. Rather than manually maintaining a separate document listing “here are all our indexes and why they exist,” which inevitably drifts out of sync with the actual schema over time, I prefer generating that documentation directly from the database using these PRAGMAs as part of a build or release script. This guarantees the documentation always reflects reality, since it’s generated from the live schema rather than hand-maintained separately from it.
Frequently Asked Questions
Does index_list show indexes from attached databases?
If you want to inspect a table in an attached database rather than the main database, you can qualify the table name accordingly, and index_list will report on that table’s indexes within the specified database context, following SQLite’s normal attached-database naming conventions.
Does the order of results from index_list mean anything?
The seq column reflects an internal ordering SQLite uses, but it isn’t something you should rely on for any particular business logic — treat the result set as an unordered collection of indexes unless you have a specific reason tied to SQLite’s internal implementation details.
Can index_list show me indexes that are currently unused by any query?
No — index_list only reports on the existence and basic metadata of indexes; it doesn’t tell you anything about whether or how often an index is actually being used by your application’s queries. For that kind of usage analysis, you’d need to combine EXPLAIN QUERY PLAN output across your actual query workload with the index inventory index_list provides.
Is there a way to see indexes across ALL tables in one single query?
Not through a single PRAGMA call, since index_list is scoped to one table per call. However, querying sqlite_master WHERE type = 'index' gives you a database-wide view in one query, at the cost of not including the structured unique/origin/partial metadata that index_list provides per index.
Wrapping Up
PRAGMA index_list is a small, focused tool, but it earns a permanent place in my regular workflow for understanding and auditing SQLite databases. It gives you a clean, structured inventory of every index on a table — including the ones SQLite creates silently behind the scenes to enforce your constraints — without requiring you to manually parse raw schema SQL. Pair it with index_info for column-level detail, and you have a complete, reliable way to understand exactly how a table is indexed before you ever touch a CREATE INDEX statement yourself.