On this page 
Warning:
CockroachDB v19.2 is no longer supported as of May 12, 2021. For more details, refer to the Release Support Policy.
The SHOW INDEX statement returns index information for a table or database.
Required privileges
The user must have any privilege on the target table or database.
Aliases
In CockroachDB, the following are aliases for SHOW INDEX:
SHOW INDEXESSHOW KEYS
Synopsis
Parameters
| Parameter | Description |
|---|---|
table_name |
The name of the table for which you want to show indexes. |
database_name |
The name of the database for which you want to show indexes. |
Response
The following fields are returned for each column in each index.
| Field | Description |
|---|---|
table_name |
The name of the table. |
index_name |
The name of the index. |
non_unique |
Whether or not values in the indexed column are unique. Possible values: true or false. |
seq_in_index |
The position of the column in the index, starting with 1. |
column_name |
The indexed column. |
direction |
How the column is sorted in the index. Possible values: ASC or DESC for indexed columns; N/A for stored columns. |
storing |
Whether or not the STORING clause was used to index the column during index creation. Possible values: true or false. |
implicit |
Whether or not the column is part of the index despite not being explicitly included during index creation. Possible values: true or falseAt this time, primary key columns are the only columns that get implicitly included in secondary indexes. The inclusion of primary key columns improves performance when retrieving columns not in the index. |
Example
Setup
The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.
To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:
$ cockroach demo
Show indexes for a table
> CREATE INDEX ON users (name);
> SHOW INDEX FROM users;
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
+------------+----------------+------------+--------------+-------------+-----------+---------+----------+
users | primary | false | 1 | city | ASC | false | false
users | primary | false | 2 | id | ASC | false | false
users | users_name_idx | true | 1 | name | ASC | false | false
users | users_name_idx | true | 2 | city | ASC | false | true
users | users_name_idx | true | 3 | id | ASC | false | true
(5 rows)
Show indexes for a database
> SHOW INDEXES FROM DATABASE movr;
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
+----------------------------+-----------------------------------------------+------------+--------------+--------------+-----------+---------+----------+
users | primary | false | 1 | city | ASC | false | false
users | primary | false | 2 | id | ASC | false | false
vehicles | primary | false | 1 | city | ASC | false | false
vehicles | primary | false | 2 | id | ASC | false | false
vehicles | vehicles_auto_index_fk_city_ref_users | true | 1 | city | ASC | false | false
vehicles | vehicles_auto_index_fk_city_ref_users | true | 2 | owner_id | ASC | false | false
vehicles | vehicles_auto_index_fk_city_ref_users | true | 3 | id | ASC | false | true
rides | primary | false | 1 | city | ASC | false | false
rides | primary | false | 2 | id | ASC | false | false
rides | rides_auto_index_fk_city_ref_users | true | 1 | city | ASC | false | false
rides | rides_auto_index_fk_city_ref_users | true | 2 | rider_id | ASC | false | false
rides | rides_auto_index_fk_city_ref_users | true | 3 | id | ASC | false | true
rides | rides_auto_index_fk_vehicle_city_ref_vehicles | true | 1 | vehicle_city | ASC | false | false
rides | rides_auto_index_fk_vehicle_city_ref_vehicles | true | 2 | vehicle_id | ASC | false | false
rides | rides_auto_index_fk_vehicle_city_ref_vehicles | true | 3 | city | ASC | false | true
rides | rides_auto_index_fk_vehicle_city_ref_vehicles | true | 4 | id | ASC | false | true
vehicle_location_histories | primary | false | 1 | city | ASC | false | false
vehicle_location_histories | primary | false | 2 | ride_id | ASC | false | false
vehicle_location_histories | primary | false | 3 | timestamp | ASC | false | false
promo_codes | primary | false | 1 | code | ASC | false | false
user_promo_codes | primary | false | 1 | city | ASC | false | false
user_promo_codes | primary | false | 2 | user_id | ASC | false | false
user_promo_codes | primary | false | 3 | code | ASC | false | false
(23 rows)
See also
Was this helpful?
