SHOW Statements 

SHOW CREATE TABLE 

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

Returns a single String-type ‘statement’ column, which contains a single value – the CREATE query used for creating the specified object.

SHOW DATABASES 

SHOW DATABASES [INTO OUTFILE filename] [FORMAT format]

Prints a list of all databases.
This query is identical to SELECT name FROM system.databases [INTO OUTFILE filename] [FORMAT format].

SHOW PROCESSLIST 

SHOW PROCESSLIST [INTO OUTFILE filename] [FORMAT format]

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.

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 TABLES 

Displays a list of tables.

SHOW [TEMPORARY] TABLES [{FROM | IN} <db>] [LIKE '<pattern>' | WHERE expr] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]

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

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

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

Example

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

SHOW TABLES FROM system LIKE '%co%' LIMIT 2
┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ collations                     │
└────────────────────────────────┘

SHOW DICTIONARIES 

Displays a list of external 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>]

Example

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 GRANTS 

Shows privileges for a user.

Syntax 

SHOW GRANTS [FOR user]

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

SHOW CREATE USER 

Shows parameters that were used at a user creation.

SHOW CREATE USER doesn’t output user passwords.

Syntax 

SHOW CREATE USER [name | CURRENT_USER]

SHOW CREATE ROLE 

Shows parameters that were used at a role creation.

Syntax 

SHOW CREATE ROLE name

SHOW CREATE ROW POLICY 

Shows parameters that were used at a row policy creation.

Syntax 

SHOW CREATE [ROW] POLICY name ON [database.]table

SHOW CREATE QUOTA 

Shows parameters that were used at a quota creation.

Syntax 

SHOW CREATE QUOTA [name | CURRENT]

SHOW CREATE SETTINGS PROFILE 

Shows parameters that were used at a settings profile creation.

Syntax 

SHOW CREATE [SETTINGS] PROFILE name

SHOW USERS 

Returns a list of user account names. To view user accounts parameters, see the system table system.users.

Syntax 

SHOW USERS

SHOW ROLES 

Returns a list of roles. To view another parameters, see system tables system.roles and system.role-grants.

Syntax 

SHOW [CURRENT|ENABLED] ROLES

SHOW PROFILES 

Returns a list of setting profiles. To view user accounts parameters, see the system table settings_profiles.

Syntax 

SHOW [SETTINGS] PROFILES

SHOW POLICIES 

Returns a list of row policies for the specified table. To view user accounts parameters, see the system table system.row_policies.

Syntax 

SHOW [ROW] POLICIES [ON [db.]table]

SHOW QUOTAS 

Returns a list of quotas. To view quotas parameters, see the system table system.quotas.

Syntax 

SHOW QUOTAS

SHOW QUOTA 

Returns a quota consumption for all users or for current user. To view another parameters, see system tables system.quotas_usage and system.quota_usage.

Syntax 

SHOW [CURRENT] QUOTA

Original article

Rating: 4.5 - 4 votes

Was this content helpful?
★★★★★