SHOW Statements

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/show.md)

Note

`SHOW CREATE (TABLE|DATABASE|USER)` hides secrets unless the following settings are turned on:

- [`display_secrets_in_show_and_select`](/docs/operations/server-configuration-parameters/settings#display_secrets_in_show_and_select) (server setting)
- [`format_display_secrets_in_show_and_select`](/docs/operations/settings/formats#format_display_secrets_in_show_and_select)  (format setting)

Additionally, the user should have the [`displaySecretsInShowAndSelect`](/docs/sql-reference/statements/grant#displaysecretsinshowandselect) privilege.

## SHOW CREATE TABLE | DICTIONARY | VIEW | DATABASE[​](#show-create-table--dictionary--view--database "Direct link to SHOW CREATE TABLE | DICTIONARY | VIEW | DATABASE")

These statements return a single column of type String,
containing the `CREATE` query used for creating the specified object.

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

```
SHOW [CREATE] TABLE | TEMPORARY TABLE | DICTIONARY | VIEW | DATABASE [db.]table|view [INTO OUTFILE filename] [FORMAT format]
```

Note

If you use this statement to get the `CREATE` query of system tables,
you will get a *fake* query, which only declares the table structure,
but cannot be used to create a table.

## SHOW DATABASES[​](#show-databases "Direct link to SHOW DATABASES")

This statement prints a list of all databases.

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

```
SHOW DATABASES [[NOT] LIKE | ILIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE filename] [FORMAT format]
```

It is identical to the query:

```
SELECT name FROM system.databases [WHERE name [NOT] LIKE | ILIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE filename] [FORMAT format]
```

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

In this example we use `SHOW` to obtain database names containing the symbol sequence 'de' in their names:

```
SHOW DATABASES LIKE '%de%'
```

```
┌─name────┐
│ default │
└─────────┘
```

We can also do so in a case-insensitive manner:

```
SHOW DATABASES ILIKE '%DE%'
```

```
┌─name────┐
│ default │
└─────────┘
```

Or get database names which do not contain 'de' in their names:

```
SHOW DATABASES NOT LIKE '%de%'
```

```
┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ system                         │
│ test                           │
│ tutorial                       │
└────────────────────────────────┘
```

Finally, we can get the names of only the first two databases:

```
SHOW DATABASES LIMIT 2
```

```
┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ default                        │
└────────────────────────────────┘
```

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

- [`CREATE DATABASE`](/docs/sql-reference/statements/create/database)

## SHOW TABLES[​](#show-tables "Direct link to SHOW TABLES")

The `SHOW TABLES` statement displays a list of tables.

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

```
SHOW [FULL] [TEMPORARY] TABLES [{FROM | IN} <db>] [[NOT] LIKE | ILIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]
```

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

This statement is identical to the query:

```
SELECT name FROM system.tables [WHERE name [NOT] LIKE | ILIKE '<pattern>'] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]
```

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

In this example we use the `SHOW TABLES` statement to find all tables containing 'user' in their names:

```
SHOW TABLES FROM system LIKE '%user%'
```

```
┌─name─────────────┐
│ user_directories │
│ users            │
└──────────────────┘
```

We can also do so in a case-insensitive manner:

```
SHOW TABLES FROM system ILIKE '%USER%'
```

```
┌─name─────────────┐
│ user_directories │
│ users            │
└──────────────────┘
```

Or to find tables which don't contain the letter 's' in their names:

```
SHOW TABLES FROM system NOT LIKE '%s%'
```

```
┌─name─────────┐
│ metric_log   │
│ metric_log_0 │
│ metric_log_1 │
└──────────────┘
```

Finally, we can get the names of only the first two tables:

```
SHOW TABLES FROM system LIMIT 2
```

```
┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ asynchronous_metric_log        │
└────────────────────────────────┘
```

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

- [`Create Tables`](/docs/sql-reference/statements/create/table)
- [`SHOW CREATE TABLE`](#show-create-table--dictionary--view--database)

## SHOW COLUMNS[​](#show_columns "Direct link to SHOW COLUMNS")

The `SHOW COLUMNS` statement displays a list of columns.

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

```
SHOW [EXTENDED] [FULL] COLUMNS {FROM | IN} <table> [{FROM | IN} <db>] [{[NOT] {LIKE | ILIKE} '<pattern>' | WHERE <expr>}] [LIMIT <N>] [INTO
OUTFILE <filename>] [FORMAT <format>]
```

The database and table name can be specified in abbreviated form as `<db>.<table>`,
meaning that `FROM tab FROM db` and `FROM db.tab` are equivalent.
If no database is specified, the query returns the list of columns from the current database.

There are also two optional keywords: `EXTENDED` and `FULL`. The `EXTENDED` keyword currently has no effect,
and exists for MySQL compatibility. The `FULL` keyword causes the output to include the collation, comment and privilege columns.

The `SHOW COLUMNS` statement produces a result table with the following structure:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Column Description Type|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `field` The name of the column `String`| `type` The column data type. If the query was made through the MySQL wire protocol, then the equivalent type name in MySQL is shown. `String`| `null` `YES` if the column data type is Nullable, `NO` otherwise `String`| `key` `PRI` if the column is part of the primary key, `SOR` if the column is part of the sorting key, empty otherwise `String`| `default` Default expression of the column if it is of type `ALIAS`, `DEFAULT`, or `MATERIALIZED`, otherwise `NULL`. `Nullable(String)`| `extra` Additional information, currently unused `String`| `collation` (only if `FULL` keyword was specified) Collation of the column, always `NULL` because ClickHouse has no per-column collations `Nullable(String)`| `comment` (only if `FULL` keyword was specified) Comment on the column `String`| `privilege` (only if `FULL` keyword was specified) The privilege you have on this column, currently not available `String` | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

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

In this example we'll use the `SHOW COLUMNS` statement to get information about all columns in table 'orders',
starting from 'delivery\_':

```
SHOW COLUMNS FROM 'orders' LIKE 'delivery_%'
```

```
┌─field───────────┬─type─────┬─null─┬─key─────┬─default─┬─extra─┐
│ delivery_date   │ DateTime │    0 │ PRI SOR │ ᴺᵁᴸᴸ    │       │
│ delivery_status │ Bool     │    0 │         │ ᴺᵁᴸᴸ    │       │
└─────────────────┴──────────┴──────┴─────────┴─────────┴───────┘
```

### 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.