# SHOW INDEX, SHOW INDEXES, SHOW KEYS

Displays the list of indexes associated with a given table.

## Syntax

```sql

SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]

```

## Remarks

* `tbl_name` - name of a table in a SingleStore database.
* `db_name` - name of a SingleStore database. If not specified, then the currently open database is used.
* `expr` - SQL WHERE expression.
* The `Key_name` for shard keys on columnstore tables is listed as "\_\_SHARDKEY" in the `SHOW INDEX`, `SHOW INDEXES`, or `SHOW KEYS` output.
* The `Index_type` for shard keys on columnstore tables is listed at "METADATA\_ONLY" in the `SHOW INDEX`, `SHOW INDEXES`, or `SHOW KEYS` output.
* This command must be run on the master aggregator or a child aggregator node (see [Node Requirements for SingleStore Commands](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md)).

**Output**

| Column          | Description                                                                                              |
| --------------- | -------------------------------------------------------------------------------------------------------- |
| `Table`         | Table name                                                                                               |
| `Non_unique`    | Non-unique                                                                                               |
| `Key_name`      | Key name                                                                                                 |
| `Seq_in_index`  | Sequence in the index                                                                                    |
| `Column_name`   | Column name                                                                                              |
| `Collation`     | Collation                                                                                                |
| `Cardinality`   | Cardinality                                                                                              |
| `Sub_part`      | Sub-part                                                                                                 |
| `Packed`        | Packed                                                                                                   |
| `Null`          | Null                                                                                                     |
| `Index_type`    | Index type (see[Index](https://docs.singlestore.com/db/v9.1/create-a-database/other-schema-concepts.md)) |
| `Comment`       | Comment                                                                                                  |
| `Index_comment` | Comment                                                                                                  |

## Example

```sql
SHOW INDEX IN mytbl;

```

```output

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytbl |          1 | seq       |            1 | seq         | NULL      |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| mytbl |          1 | seq_index |            1 | seq         | NULL      |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

```

```sql
SHOW INDEXES IN mytbl;

```

```output

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytbl |          1 | seq       |            1 | seq         | NULL      |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| mytbl |          1 | seq_index |            1 | seq         | NULL      |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
```

```sql
SHOW KEYS in mytbl;

```

```output

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytbl |          1 | seq       |            1 | seq         | NULL      |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| mytbl |          1 | seq_index |            1 | seq         | NULL      |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

```

```sql
SHOW INDEXES in my_columnstore_tbl;

```

```output

+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+---------------+----------+---------------+
| Table              | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type    | Comment  | Index_comment | 
+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+---------------+----------+---------------+
| my_columnstore_tbl |          1 | __SHARDKEY |            1 | a           | NULL      |        NULL |     NULL | NULL   | YES  | METADATA_ONLY |          |               |
+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+---------------+----------+---------------+

```

***

Modified at: December 14, 2023

Source: [/db/v9.1/reference/sql-reference/show-commands/show-index-show-indexes-show-keys/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-index-show-indexes-show-keys/)

(An index of the documentation is available at /llms.txt)