SHOW Queries

SHOW CREATE TABLE

SHOW CREATE [TEMPORARY] TABLE [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 table.

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

See also the section "Formats".

SHOW PROCESSLIST

SHOW PROCESSLIST [INTO OUTFILE filename] [FORMAT format]

Outputs a list of queries currently being processed, other than SHOW PROCESSLIST queries.

Prints a table containing the columns:

user – The user who made the query. Keep in mind that for distributed processing, queries are sent to remote servers under the 'default' user. SHOW PROCESSLIST shows the username for a specific query, not for a query that this query initiated.

address – The name of the host that the query was sent from. For distributed processing, on remote servers, this is the name of the query requestor host. To track where a distributed query was originally made from, look at SHOW PROCESSLIST on the query requestor server.

elapsed – The execution time, in seconds. Queries are output in order of decreasing execution time.

rows_read, bytes_read – How many rows and bytes of uncompressed data were read when processing the query. For distributed processing, data is totaled from all the remote servers. This is the data used for restrictions and quotas.

memory_usage – Current RAM usage in bytes. See the setting 'max_memory_usage'.

query – The query itself. In INSERT queries, the data for insertion is not output.

query_id – The query identifier. Non-empty only if it was explicitly defined by the user. For distributed processing, the query ID is not passed to remote servers.

This query is nearly identical to: SELECT * FROM system.processes. The difference is that the SHOW PROCESSLIST query does not show itself in a list, when the SELECT .. FROM system.processes query does.

Tip (execute in the console):

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

SHOW TABLES

Displays a list of tables.

SHOW [TEMPORARY] TABLES [FROM <db>] [LIKE '<pattern>'] [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.

The same result as the SHOW TABLES query returns, you can get by 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                     │
└────────────────────────────────┘