> ## Documentation Index > Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # SHOW INDEX export const InternalLink = ({version, path = "", children, ...props}) => { let detectedVersion = version || "stable"; if (typeof window !== 'undefined' && !version) { const match = window.location.pathname.match(/\/docs\/([^/]+)/); if (match) { detectedVersion = match[1]; } } const normalizedPath = path.startsWith("/") ? path.slice(1) : path; return {children} ; }; 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 INDEXES` * `SHOW KEYS` ## Synopsis show_indexes syntax diagram ## 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. Possible values: `true` or `false`. | | `implicit` | Whether the column is part of the index despite not being explicitly included during index creation. Possible values: `true` or `false`

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. | 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` to start a temporary, in-memory cluster with the `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` * `COMMENT ON` * `DROP INDEX` * `ALTER INDEX ... RENAME TO` * Information Schema * SQL Statements