In this article I outline two ways to return a list of indexes in an SQLite database.

The first (and most obvious) method is to use the .indexes dot command. The second method is to query the sql_master table.

The .indexes Command

Here’s an example of using the .indexes command on the Chinook sample database.

.indexes

Result:

IFK_AlbumArtistId                 IFK_PlaylistTrackTrackId
IFK_CustomerSupportRepId          IFK_TrackAlbumId
IFK_EmployeeReportsTo             IFK_TrackGenreId
IFK_InvoiceCustomerId             IFK_TrackMediaTypeId
IFK_InvoiceLineInvoiceId          sqlite_autoindex_PlaylistTrack_1
IFK_InvoiceLineTrackId          

You can also provide an argument to specify which index/indexes you’d like returned. You can provide the full name of the index or you could use pattern matching to return all indexes that match that pattern.

Example of using pattern matching:

.indexes %invoice%

Result:

IFK_InvoiceCustomerId     IFK_InvoiceLineInvoiceId  IFK_InvoiceLineTrackId  

The sqlite_master Table

As an alternative to the .indexes command, you can run a query against the sql_master table.

This table contains more than just indexes, but you can use a WHERE clause to narrow it down to just indexes:

SELECT name 
FROM sqlite_master 
WHERE type = 'index';

Result:

IFK_AlbumArtistId
sqlite_autoindex_PlaylistTrack_1
IFK_CustomerSupportRepId
IFK_EmployeeReportsTo
IFK_InvoiceCustomerId
IFK_InvoiceLineInvoiceId
IFK_InvoiceLineTrackId
IFK_PlaylistTrackTrackId
IFK_TrackAlbumId
IFK_TrackGenreId
IFK_TrackMediaTypeId

One benefit of using this method is that you can also return the table to which each index belongs. This is stored in the tbl_name column.

Example:

.mode column
.headers on
.width 32 13
SELECT 
  name,
  tbl_name
FROM sqlite_master 
WHERE type = 'index';

Return:

name                              tbl_name     
--------------------------------  -------------
IFK_AlbumArtistId                 Album        
sqlite_autoindex_PlaylistTrack_1  PlaylistTrack
IFK_CustomerSupportRepId          Customer     
IFK_EmployeeReportsTo             Employee     
IFK_InvoiceCustomerId             Invoice      
IFK_InvoiceLineInvoiceId          InvoiceLine  
IFK_InvoiceLineTrackId            InvoiceLine  
IFK_PlaylistTrackTrackId          PlaylistTrack
IFK_TrackAlbumId                  Track        
IFK_TrackGenreId                  Track        
IFK_TrackMediaTypeId              Track