SHOW Statements
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.
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
SHOW [CREATE] [TEMPORARY] TABLE|DICTIONARY|VIEW|DATABASE [db.]table|view [INTO OUTFILE filename] [FORMAT format]
SHOW TABLE t and SHOW DATABASE db have the same meaning as SHOW CREATE TABLE|DATABASE t|db, but SHOW t and SHOW db are not supported.
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
This statement prints a list of all databases.
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
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
SHOW TABLES
The SHOW TABLES statement displays a list of tables.
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
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
SHOW COLUMNS
The SHOW COLUMNS statement displays a list of columns.
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
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
SHOW DICTIONARIES
The SHOW DICTIONARIES statement displays a list of Dictionaries.
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
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
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.
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
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
SHOW PROCESSLIST
Outputs the content of the system.processes table, that contains a list of queries that is being processed at the moment, excepting SHOW PROCESSLIST queries.
Syntax
SHOW PROCESSLIST [INTO OUTFILE filename] [FORMAT format]
The SELECT * FROM system.processes query returns data about all the current queries.
Execute in the console:
$ watch -n1 "clickhouse-client --query='SHOW PROCESSLIST'"
SHOW GRANTS
The SHOW GRANTS statement shows privileges for a user.
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
The SHOW CREATE USER statement shows parameters which were used at user creation.
Syntax
SHOW CREATE USER [name1 [, name2 ...] | CURRENT_USER]
SHOW CREATE ROLE
The SHOW CREATE ROLE statement shows parameters which were used at role creation.
Syntax
SHOW CREATE ROLE name1 [, name2 ...]
SHOW CREATE ROW POLICY
The SHOW CREATE ROW POLICY statement shows parameters which were used at row policy creation.
Syntax
SHOW CREATE [ROW] POLICY name ON [database1.]table1 [, [database2.]table2 ...]
SHOW CREATE QUOTA
The SHOW CREATE QUOTA statement shows parameters which were used at quota creation.
Syntax
SHOW CREATE QUOTA [name1 [, name2 ...] | CURRENT]
SHOW CREATE SETTINGS PROFILE
The SHOW CREATE SETTINGS PROFILE statement shows parameters which were used at settings profile creation.
Syntax
SHOW CREATE [SETTINGS] PROFILE name1 [, name2 ...]
SHOW USERS
The SHOW USERS statement returns a list of user account names.
To view user accounts parameters, see the system table system.users.
Syntax
SHOW USERS
SHOW ROLES
The SHOW ROLES statement returns a list of roles.
To view other parameters,
see system tables system.roles and system.role_grants.
Syntax
SHOW [CURRENT|ENABLED] ROLES
SHOW PROFILES
The SHOW PROFILES statement returns a list of setting profiles.
To view user accounts parameters, see system table settings_profiles.
Syntax
SHOW [SETTINGS] PROFILES
SHOW POLICIES
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.
Syntax
SHOW [ROW] POLICIES [ON [db.]table]
SHOW QUOTAS
The SHOW QUOTAS statement returns a list of quotas.
To view quotas parameters, see the system table system.quotas.
Syntax
SHOW QUOTAS
SHOW QUOTA
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.
Syntax
SHOW [CURRENT] QUOTA
SHOW ACCESS
The SHOW ACCESS statement shows all users, roles, profiles, etc. and all their grants.
Syntax
SHOW ACCESS
SHOW CLUSTER(S)
The SHOW CLUSTER(S) statement returns a list of clusters.
All available clusters are listed in the system.clusters table.
The SHOW CLUSTER name query displays the contents of system.clusters table for the specified cluster name.
Syntax
SHOW CLUSTER '<name>'
SHOW CLUSTERS [[NOT] LIKE|ILIKE '<pattern>'] [LIMIT <N>]
Examples
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
shard_weight: 1
replica_num: 1
host_name: localhost
host_address: 127.0.0.1
port: 9000
is_local: 1
user: default
default_database:
errors_count: 0
estimated_recovery_time: 0
SHOW SETTINGS
The SHOW SETTINGS statement returns a list of system settings and their values.
It selects data from the system.settings table.
Syntax
SHOW [CHANGED] SETTINGS LIKE|ILIKE <name>
Clauses
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.
Examples
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 │
└──────────────────┴────────┴─────────────┘
SHOW SETTING
The SHOW SETTING statement outputs setting value for specified setting name.
Syntax
SHOW SETTING <name>
See also
system.settingstable
SHOW FILESYSTEM CACHES
Examples
SHOW FILESYSTEM CACHES
┌─Caches────┐
│ s3_cache │
└───────────┘
See also
system.settingstable
SHOW ENGINES
The 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.
Syntax
SHOW ENGINES [INTO OUTFILE filename] [FORMAT format]
See also
- system.table_engines table
SHOW FUNCTIONS
The SHOW FUNCTIONS statement outputs the content of the system.functions table.
Syntax
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>.
See Also
system.functionstable
SHOW MERGES
The 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. |
Syntax
SHOW MERGES [[NOT] LIKE|ILIKE '<table_name_pattern>'] [LIMIT <N>]
Examples
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 │
└────────────┴──────────┴───────────────────┴─────────┴──────────┴─────────────┴─────────────────┴──────────────┘