SQLite | Indexes | Viewing an Index Schema

Tags:

This article explains how to find the CREATE INDEX statement used to create an index. You can query the sqlite_master table or use the .schema command.

Querying the sqlite_master Table

Run the following SQL statement. The example first changes the output mode to line with .mode for readability.

select * from sqlite_master;

sqlite> select * from sqlite_master;
table|user|user|2|CREATE TABLE user (name text, old integer, address text, colunm name)
index|nameindex|user|3|CREATE UNIQUE INDEX nameindex on user (name)
sqlite> 
sqlite> .mode line
sqlite> 
sqlite> select * from sqlite_master;
    type = table
    name = user
tbl_name = user
rootpage = 2
     sql = CREATE TABLE user (name text, old integer, address text)

    type = index
    name = nameindex
tbl_name = user
rootpage = 3
     sql = CREATE UNIQUE INDEX nameindex on user (name)
sqlite>

The result contains data for the created tables and indexes. The index entry is as follows.

    type = index
    name = nameindex
tbl_name = user
rootpage = 3
     sql = CREATE UNIQUE INDEX nameindex on user (name)

For an index, type is index rather than table. The name column contains the index name, tbl_name contains its table name, and sql contains the statement that created it.

Use a WHERE clause to display only indexes.

select * from sqlite_master where type = 'index';

sqlite> select * from sqlite_master where type = 'index';
    type = index
    name = nameindex
tbl_name = user
rootpage = 3
     sql = CREATE UNIQUE INDEX nameindex on user (name)

Using the .schema Command

The SQLite .schema command also displays schema information.

.schema
.schema? TABLE?

Without an argument it displays all table and index schemas. With a table name, it displays schema information associated with that table or a matching index name.

Run the command as follows.

.schema

sqlite> .schema
CREATE TABLE user (name text, old integer, address text, colunm name);
CREATE UNIQUE INDEX nameindex on user (name);
sqlite>

The output shows the CREATE statements for the tables and indexes in the current database.