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 (server setting)format_display_secrets_in_show_and_select (format setting)Additionally, the user should have the displaySecretsInShowAndSelect privilege.
:::
These statements return a single column of type String,
containing the CREATE query used for creating the specified object.
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.
:::
This statement prints a list of all databases.
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]
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 │
└────────────────────────────────┘
The SHOW TABLES statement displays a list of tables.
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>]
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 │
└────────────────────────────────┘
The SHOW COLUMNS statement displays a list of columns.
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 |
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 │ │ ᴺᵁᴸᴸ │ │
└─────────────────┴──────────┴──────┴─────────┴─────────┴───────┘
The SHOW DICTIONARIES statement displays a list of Dictionaries.
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>]
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 │
└──────────────┘
Displays a list of primary and data skipping indexes of a table.
This statement mostly exists for compatibility with MySQL. System tables system.tables (for
primary keys) and system.data_skipping_indices (for data skipping indices)
provide equivalent information but in a fashion more native to ClickHouse.
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 |
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 │
└───────┴────────────┴──────────┴──────────────┴─────────────┴───────────┴─────────────┴──────────┴────────┴──────┴──────────────┴─────────┴───────────────┴─────────┴────────────┘
Outputs the content of the system.processes table, that contains a list of queries that are being processed at the moment, excluding SHOW PROCESSLIST queries.
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'"
:::
The SHOW GRANTS statement shows privileges for a user.
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)
The SHOW CREATE USER statement shows parameters which were used at user creation.
SHOW CREATE USER [name1 [, name2 ...] | CURRENT_USER]
The SHOW CREATE ROLE statement shows parameters which were used at role creation.
SHOW CREATE ROLE name1 [, name2 ...]
The SHOW CREATE ROW POLICY statement shows parameters which were used at row policy creation.
SHOW CREATE [ROW] POLICY name ON [database1.]table1 [, [database2.]table2 ...]
The SHOW CREATE QUOTA statement shows parameters which were used at quota creation.
SHOW CREATE QUOTA [name1 [, name2 ...] | CURRENT]
The SHOW CREATE SETTINGS PROFILE statement shows parameters which were used at settings profile creation.
SHOW CREATE [SETTINGS] PROFILE name1 [, name2 ...]
The SHOW USERS statement returns a list of user account names.
To view user accounts parameters, see the system table system.users.
SHOW USERS
The SHOW ROLES statement returns a list of roles.
To view other parameters,
see system tables system.roles and system.role_grants.
SHOW [CURRENT|ENABLED] ROLES
The SHOW PROFILES statement returns a list of setting profiles.
To view user accounts parameters, see system table settings_profiles.
SHOW [SETTINGS] PROFILES
The SHOW POLICIES statement returns a list of row policies for the specified table.
To view user accounts parameters, see system table system.row_policies.
SHOW [ROW] POLICIES [ON [db.]table]
The SHOW QUOTAS statement returns a list of quotas.
To view quotas parameters, see the system table system.quotas.
SHOW QUOTAS
The SHOW QUOTA statement returns a quota consumption for all users or for current user.
To view other parameters, see system tables system.quotas_usage and system.quota_usage.
SHOW [CURRENT] QUOTA
The SHOW ACCESS statement shows all users, roles, profiles, etc. and all their grants.
SHOW ACCESS
The SHOW CLUSTER(S) statement returns a list of clusters.
All available clusters are listed in the system.clusters table.
:::note
The SHOW CLUSTER name query displays cluster, shard_num, replica_num, host_name, host_address, and port of the system.clusters table for the specified cluster name.
:::
SHOW CLUSTER '<name>'
SHOW CLUSTERS [[NOT] LIKE|ILIKE '<pattern>'] [LIMIT <N>]
SHOW CLUSTERS;
┌─cluster──────────────────────────────────────┐
│ test_cluster_two_shards │
│ test_cluster_two_shards_internal_replication │
│ test_cluster_two_shards_localhost │
│ test_shard_localhost │
│ test_shard_localhost_secure │
│ test_unavailable_shard │
└──────────────────────────────────────────────┘
SHOW CLUSTERS LIKE 'test%' LIMIT 1;
┌─cluster─────────────────┐
│ test_cluster_two_shards │
└─────────────────────────┘
SHOW CLUSTER 'test_shard_localhost' FORMAT Vertical;
Row 1:
──────
cluster: test_shard_localhost
shard_num: 1
replica_num: 1
host_name: localhost
host_address: 127.0.0.1
port: 9000
The SHOW SETTINGS statement returns a list of system settings and their values.
It selects data from the system.settings table.
SHOW [CHANGED] SETTINGS LIKE|ILIKE <name>
LIKE|ILIKE allow to specify a matching pattern for the setting name. It can contain globs such as % or _. LIKE clause is case-sensitive, ILIKE — case insensitive.
When the CHANGED clause is used, the query returns only settings changed from their default values.
Query with the LIKE clause:
SHOW SETTINGS LIKE 'send_timeout';
┌─name─────────┬─type────┬─value─┐
│ send_timeout │ Seconds │ 300 │
└──────────────┴─────────┴───────┘
Query with the ILIKE clause:
SHOW SETTINGS ILIKE '%CONNECT_timeout%'
┌─name────────────────────────────────────┬─type─────────┬─value─┐
│ connect_timeout │ Seconds │ 10 │
│ connect_timeout_with_failover_ms │ Milliseconds │ 50 │
│ connect_timeout_with_failover_secure_ms │ Milliseconds │ 100 │
└─────────────────────────────────────────┴──────────────┴───────┘
Query with the CHANGED clause:
SHOW CHANGED SETTINGS ILIKE '%MEMORY%'
┌─name─────────────┬─type───┬─value───────┐
│ max_memory_usage │ UInt64 │ 10000000000 │
└──────────────────┴────────┴─────────────┘
The SHOW SETTING statement outputs setting value for specified setting name.
SHOW SETTING <name>
system.settings tableSHOW FILESYSTEM CACHES
┌─Caches────┐
│ s3_cache │
└───────────┘
system.settings tableThe SHOW ENGINES statement outputs the content of the system.table_engines table,
that contains description of table engines supported by server and their feature support information.
SHOW ENGINES [INTO OUTFILE filename] [FORMAT format]
The SHOW FUNCTIONS statement outputs the content of the system.functions table.
SHOW FUNCTIONS [LIKE | ILIKE '<pattern>']
If either LIKE or ILIKE clause is specified, the query returns a list of system functions whose names match the provided <pattern>.
system.functions tableThe SHOW MERGES statement returns a list of merges.
All merges are listed in the system.merges table:
| Column | Description |
|---|---|
table | Table name. |
database | The name of the database the table is in. |
estimate_complete | The estimated time to complete (in seconds). |
elapsed | The time elapsed (in seconds) since the merge started. |
progress | The percentage of completed work (0-100 percent). |
is_mutation | 1 if this process is a part mutation. |
size_compressed | The total size of the compressed data of the merged parts. |
memory_usage | Memory consumption of the merge process. |
SHOW MERGES [[NOT] LIKE|ILIKE '<table_name_pattern>'] [LIMIT <N>]
SHOW MERGES;
┌─table──────┬─database─┬─estimate_complete─┬─elapsed─┬─progress─┬─is_mutation─┬─size_compressed─┬─memory_usage─┐
│ your_table │ default │ 0.14 │ 0.36 │ 73.01 │ 0 │ 5.40 MiB │ 10.25 MiB │
└────────────┴──────────┴───────────────────┴─────────┴──────────┴─────────────┴─────────────────┴──────────────┘
SHOW MERGES LIKE 'your_t%' LIMIT 1;
┌─table──────┬─database─┬─estimate_complete─┬─elapsed─┬─progress─┬─is_mutation─┬─size_compressed─┬─memory_usage─┐
│ your_table │ default │ 0.14 │ 0.36 │ 73.01 │ 0 │ 5.40 MiB │ 10.25 MiB │
└────────────┴──────────┴───────────────────┴─────────┴──────────┴─────────────┴─────────────────┴──────────────┘
The SHOW CREATE MASKING POLICY statement shows parameters which were used at masking policy creation.
SHOW CREATE MASKING POLICY name ON [database.]table