SHOW Statements

### See also[​](#see-also-2 "Direct link to See also")

- [`system.columns`](/docs/operations/system-tables/columns)

## SHOW DICTIONARIES[​](#show-dictionaries "Direct link to SHOW DICTIONARIES")

The `SHOW DICTIONARIES` statement displays a list of [Dictionaries](/docs/sql-reference/statements/create/dictionary).

### Syntax[​](#syntax-4 "Direct link to Syntax")

```
SHOW DICTIONARIES [FROM <db>] [LIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]
```

If the `FROM` clause is not specified, the query returns the list of dictionaries from the current database.

You can get the same results as the `SHOW DICTIONARIES` query in the following way:

```
SELECT name FROM system.dictionaries WHERE database = <db> [AND name LIKE <pattern>] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]
```

### Examples[​](#examples-3 "Direct link to Examples")

The following query selects the first two rows from the list of tables in the `system` database, whose names contain `reg`.

```
SHOW DICTIONARIES FROM db LIKE '%reg%' LIMIT 2
```

```
┌─name─────────┐
│ regions      │
│ region_names │
└──────────────┘
```

## SHOW INDEX[​](#show-index "Direct link to SHOW INDEX")

Displays a list of primary and data skipping indexes of a table.

This statement mostly exists for compatibility with MySQL. System tables [`system.tables`](/docs/operations/system-tables/tables) (for
primary keys) and [`system.data_skipping_indices`](/docs/operations/system-tables/data_skipping_indices) (for data skipping indices)
provide equivalent information but in a fashion more native to ClickHouse.

### Syntax[​](#syntax-5 "Direct link to Syntax")

```
SHOW [EXTENDED] {INDEX | INDEXES | INDICES | KEYS } {FROM | IN} <table> [{FROM | IN} <db>] [WHERE <expr>] [INTO OUTFILE <filename>] [FORMAT <format>]
```

The database and table name can be specified in abbreviated form as `<db>.<table>`, i.e. `FROM tab FROM db` and `FROM db.tab` are
equivalent. If no database is specified, the query assumes the current database as database.

The optional keyword `EXTENDED` currently has no effect, and exists for MySQL compatibility.

The statement produces a result table with the following structure:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Column Description Type|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `table` The name of the table. `String`| `non_unique` Always `1` as ClickHouse does not support uniqueness constraints. `UInt8`| `key_name` The name of the index, `PRIMARY` if the index is a primary key index. `String`| `seq_in_index` For a primary key index, the position of the column starting from `1`. For a data skipping index: always `1`. `UInt8`| `column_name` For a primary key index, the name of the column. For a data skipping index: `''` (empty string), see field "expression". `String`| `collation` The sorting of the column in the index: `A` if ascending, `D` if descending, `NULL` if unsorted. `Nullable(String)`| `cardinality` An estimation of the index cardinality (number of unique values in the index). Currently always 0. `UInt64`| `sub_part` Always `NULL` because ClickHouse does not support index prefixes like MySQL. `Nullable(String)`| `packed` Always `NULL` because ClickHouse does not support packed indexes (like MySQL). `Nullable(String)`| `null` Currently unused |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `index_type` The index type, e.g. `PRIMARY`, `MINMAX`, `BLOOM_FILTER` etc. `String`| `comment` Additional information about the index, currently always `''` (empty string). `String`| `index_comment` `''` (empty string) because indexes in ClickHouse cannot have a `COMMENT` field (like in MySQL). `String`| `visible` If the index is visible to the optimizer, always `YES`. `String`| `expression` For a data skipping index, the index expression. For a primary key index: `''` (empty string). `String` | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Examples[​](#examples-4 "Direct link to Examples")

In this example we use the `SHOW INDEX` statement to get information about all indexes in table 'tbl'

```
SHOW INDEX FROM 'tbl'
```

```
┌─table─┬─non_unique─┬─key_name─┬─seq_in_index─┬─column_name─┬─collation─┬─cardinality─┬─sub_part─┬─packed─┬─null─┬─index_type───┬─comment─┬─index_comment─┬─visible─┬─expression─┐
│ tbl   │          1 │ blf_idx  │ 1            │ 1           │ ᴺᵁᴸᴸ      │ 0           │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ BLOOM_FILTER │         │               │ YES     │ d, b       │
│ tbl   │          1 │ mm1_idx  │ 1            │ 1           │ ᴺᵁᴸᴸ      │ 0           │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ MINMAX       │         │               │ YES     │ a, c, d    │
│ tbl   │          1 │ mm2_idx  │ 1            │ 1           │ ᴺᵁᴸᴸ      │ 0           │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ MINMAX       │         │               │ YES     │ c, d, e    │
│ tbl   │          1 │ PRIMARY  │ 1            │ c           │ A         │ 0           │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ PRIMARY      │         │               │ YES     │            │
│ tbl   │          1 │ PRIMARY  │ 2            │ a           │ A         │ 0           │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ PRIMARY      │         │               │ YES     │            │
│ tbl   │          1 │ set_idx  │ 1            │ 1           │ ᴺᵁᴸᴸ      │ 0           │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ │ SET          │         │               │ YES     │ e          │
└───────┴────────────┴──────────┴──────────────┴─────────────┴───────────┴─────────────┴──────────┴────────┴──────┴──────────────┴─────────┴───────────────┴─────────┴────────────┘
```

### See also[​](#see-also-3 "Direct link to See also")

- [`system.tables`](/docs/operations/system-tables/tables)
- [`system.data_skipping_indices`](/docs/operations/system-tables/data_skipping_indices)

## SHOW PROCESSLIST[​](#show-processlist "Direct link to SHOW PROCESSLIST")

Outputs the content of the [`system.processes`](/docs/operations/system-tables/processes) table, that contains a list of queries that are being processed at the moment, excluding `SHOW PROCESSLIST` queries.

### Syntax[​](#syntax-6 "Direct link to Syntax")

```
SHOW PROCESSLIST [INTO OUTFILE filename] [FORMAT format]
```

The `SELECT * FROM system.processes` query returns data about all the current queries.

Tip

Execute in the console:

```
$ watch -n1 "clickhouse-client --query='SHOW PROCESSLIST'"
```

## SHOW GRANTS[​](#show-grants "Direct link to SHOW GRANTS")

The `SHOW GRANTS` statement shows privileges for a user.

### Syntax[​](#syntax-7 "Direct link to Syntax")

```
SHOW GRANTS [FOR user1 [, user2 ...]] [WITH IMPLICIT] [FINAL]
```

If the user is not specified, the query returns privileges for the current user.

The `WITH IMPLICIT` modifier allows showing the implicit grants (e.g., `GRANT SELECT ON system.one`)

The `FINAL` modifier merges all grants from the user and its granted roles (with inheritance)

## SHOW CREATE USER[​](#show-create-user "Direct link to SHOW CREATE USER")

The `SHOW CREATE USER` statement shows parameters which were used at [user creation](/docs/sql-reference/statements/create/user).

### Syntax[​](#syntax-8 "Direct link to Syntax")

```
SHOW CREATE USER [name1 [, name2 ...] | CURRENT_USER]
```

## SHOW CREATE ROLE[​](#show-create-role "Direct link to SHOW CREATE ROLE")