> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # SHOW INDEX The `SHOW INDEX` [statement](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) returns index information for a table or database. ## Required privileges The user must have any [privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#managing-privileges) on the target table or database. ## Aliases In CockroachDB, the following are aliases for `SHOW INDEX`: * `SHOW INDEXES` * `SHOW KEYS` ## Synopsis ![show\_indexes syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/show_indexes.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=ddc9959bb57f4ef9a9d1cf2ae5434294) ## Parameters | Parameter | Description | | - | - | | `table_name` | The name of the table for which to show indexes. | | `database_name` | The name of the database for which 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 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 the `STORING` clause was used to index the column during [index creation](https://docs.cockroachlabs.com/docs/v24.2/create-index). Possible values: `true` or `false`. | | `implicit` | Whether the column is part of the index despite not being explicitly included during [index creation](https://docs.cockroachlabs.com/docs/v24.2/create-index). Possible values: `true` or `false` [Primary key](https://docs.cockroachlabs.com/docs/v24.2/primary-key) columns are the only columns implicitly included in secondary indexes. The inclusion of primary key columns improves performance when retrieving columns not in the index. | | `visible` | Whether the index is visible to the [cost-based optimizer](https://docs.cockroachlabs.com/docs/v24.2/cost-based-optimizer#control-whether-the-optimizer-uses-an-index). | A column is in the primary key if the value of the `index_name` column is `{tbl}_pkey` and value of the `storing` column is `false`. ## Example #### Setup To follow along, run [`cockroach demo`](https://docs.cockroachlabs.com/docs/v24.2/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](https://docs.cockroachlabs.com/docs/v24.2/movr) sample dataset preloaded: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo ``` ### Show indexes for a table ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE INDEX ON users (name); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW INDEX FROM users; ``` ``` table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible -------------+----------------+------------+--------------+-------------+-----------+---------+----------+---------- users | users_name_idx | t | 1 | name | ASC | f | f | t users | users_name_idx | t | 2 | city | ASC | f | t | t users | users_name_idx | t | 3 | id | ASC | f | t | t users | users_pkey | f | 1 | city | ASC | f | f | t users | users_pkey | f | 2 | id | ASC | f | f | t users | users_pkey | f | 3 | name | N/A | t | f | t users | users_pkey | f | 4 | address | N/A | t | f | t users | users_pkey | f | 5 | credit_card | N/A | t | f | t (8 rows) ``` In this example, the columns where the value of the `index_name` column is `users_pkey` and value of the `storing` column is `false`, and thus are in the primary key, are `city` and `id`. ### Show indexes for a database ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW INDEXES FROM DATABASE movr; ``` ``` table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible -----------------------------+-----------------------------------------------+------------+--------------+------------------+-----------+---------+----------+---------- promo_codes | promo_codes_pkey | f | 1 | code | ASC | f | f | t promo_codes | promo_codes_pkey | f | 2 | description | N/A | t | f | t promo_codes | promo_codes_pkey | f | 3 | creation_time | N/A | t | f | t promo_codes | promo_codes_pkey | f | 4 | expiration_time | N/A | t | f | t promo_codes | promo_codes_pkey | f | 5 | rules | N/A | t | f | t rides | rides_auto_index_fk_city_ref_users | t | 1 | city | ASC | f | f | t rides | rides_auto_index_fk_city_ref_users | t | 2 | rider_id | ASC | f | f | t rides | rides_auto_index_fk_city_ref_users | t | 3 | id | ASC | f | t | t rides | rides_auto_index_fk_vehicle_city_ref_vehicles | t | 1 | vehicle_city | ASC | f | f | t rides | rides_auto_index_fk_vehicle_city_ref_vehicles | t | 2 | vehicle_id | ASC | f | f | t rides | rides_auto_index_fk_vehicle_city_ref_vehicles | t | 3 | city | ASC | f | t | t rides | rides_auto_index_fk_vehicle_city_ref_vehicles | t | 4 | id | ASC | f | t | t rides | rides_pkey | f | 1 | city | ASC | f | f | t rides | rides_pkey | f | 2 | id | ASC | f | f | t rides | rides_pkey | f | 3 | vehicle_city | N/A | t | f | t rides | rides_pkey | f | 4 | rider_id | N/A | t | f | t rides | rides_pkey | f | 5 | vehicle_id | N/A | t | f | t rides | rides_pkey | f | 6 | start_address | N/A | t | f | t rides | rides_pkey | f | 7 | end_address | N/A | t | f | t rides | rides_pkey | f | 8 | start_time | N/A | t | f | t rides | rides_pkey | f | 9 | end_time | N/A | t | f | t rides | rides_pkey | f | 10 | revenue | N/A | t | f | t user_promo_codes | user_promo_codes_pkey | f | 1 | city | ASC | f | f | t user_promo_codes | user_promo_codes_pkey | f | 2 | user_id | ASC | f | f | t user_promo_codes | user_promo_codes_pkey | f | 3 | code | ASC | f | f | t user_promo_codes | user_promo_codes_pkey | f | 4 | timestamp | N/A | t | f | t user_promo_codes | user_promo_codes_pkey | f | 5 | usage_count | N/A | t | f | t users | users_name_idx | t | 1 | name | ASC | f | f | t users | users_name_idx | t | 2 | city | ASC | f | t | t users | users_name_idx | t | 3 | id | ASC | f | t | t users | users_pkey | f | 1 | city | ASC | f | f | t users | users_pkey | f | 2 | id | ASC | f | f | t users | users_pkey | f | 3 | name | N/A | t | f | t users | users_pkey | f | 4 | address | N/A | t | f | t users | users_pkey | f | 5 | credit_card | N/A | t | f | t vehicle_location_histories | vehicle_location_histories_pkey | f | 1 | city | ASC | f | f | t vehicle_location_histories | vehicle_location_histories_pkey | f | 2 | ride_id | ASC | f | f | t vehicle_location_histories | vehicle_location_histories_pkey | f | 3 | timestamp | ASC | f | f | t vehicle_location_histories | vehicle_location_histories_pkey | f | 4 | lat | N/A | t | f | t vehicle_location_histories | vehicle_location_histories_pkey | f | 5 | long | N/A | t | f | t vehicles | vehicles_auto_index_fk_city_ref_users | t | 1 | city | ASC | f | f | t vehicles | vehicles_auto_index_fk_city_ref_users | t | 2 | owner_id | ASC | f | f | t vehicles | vehicles_auto_index_fk_city_ref_users | t | 3 | id | ASC | f | t | t vehicles | vehicles_pkey | f | 1 | city | ASC | f | f | t vehicles | vehicles_pkey | f | 2 | id | ASC | f | f | t vehicles | vehicles_pkey | f | 3 | type | N/A | t | f | t vehicles | vehicles_pkey | f | 4 | owner_id | N/A | t | f | t vehicles | vehicles_pkey | f | 5 | creation_time | N/A | t | f | t vehicles | vehicles_pkey | f | 6 | status | N/A | t | f | t vehicles | vehicles_pkey | f | 7 | current_location | N/A | t | f | t vehicles | vehicles_pkey | f | 8 | ext | N/A | t | f | t (51 rows) ``` ## See also * [`CREATE INDEX`](https://docs.cockroachlabs.com/docs/v24.2/create-index) * [`COMMENT ON`](https://docs.cockroachlabs.com/docs/v24.2/comment-on) * [`DROP INDEX`](https://docs.cockroachlabs.com/docs/v24.2/drop-index) * [`ALTER INDEX ... RENAME TO`](https://docs.cockroachlabs.com/docs/v24.2/alter-index#rename-to) * [Information Schema](https://docs.cockroachlabs.com/docs/v24.2/information-schema) * [SQL Statements](https://docs.cockroachlabs.com/docs/v24.2/sql-statements)