How to List Tables in ClickHouse

Galaxy Glossary

How do I list tables in ClickHouse?

SHOW TABLES returns the names of all tables in the current or specified ClickHouse database.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

How do I quickly list all tables?

Run SHOW TABLES in your active database session. ClickHouse immediately returns every table name in that database.

How do I target a specific database?

Use SHOW TABLES FROM ecommerce (or IN ecommerce). This prevents accidental scans across the wrong schema when you work with multiple databases.

Can I filter table names by pattern?

Append LIKE or WHERE. Example: SHOW TABLES FROM ecommerce LIKE 'ord%' lists only tables whose names start with ord.

Why use WHERE over LIKE?

WHERE supports full expressions: SHOW TABLES FROM ecommerce WHERE name NOT LIKE '%tmp%' skips temporary tables, improving readability.

How do I verify the table list programmatically?

Query system.tables: SELECT name FROM system.tables WHERE database = 'ecommerce'. This returns the same data and lets you join with other system views.

Best practices for production schemas

Prefix tables (e.g., customers_, orders_) so pattern filters remain reliable. Keep staging tables in separate databases to avoid noisy SHOW TABLES outputs.

How does this help query authors?

Listing tables before writing joins reduces typos and speeds up Galaxy’s AI autocompletion. Confirm table existence, naming, and case sensitivity up front.

Why How to List Tables in ClickHouse is important

How to List Tables in ClickHouse Example Usage


-- Confirm all ecommerce objects exist before building a sales report
SHOW TABLES FROM ecommerce;
-- Expected output
-- | name       |
-- | Customers  |
-- | Orders     |
-- | Products   |
-- | OrderItems |

How to List Tables in ClickHouse Syntax


SHOW TABLES [IN | FROM] [<database>] [LIKE '<pattern>'] [WHERE <expr>] [LIMIT <n>];

-- Ecommerce examples
SHOW TABLES; -- list tables in current DB
SHOW TABLES FROM ecommerce; -- list in ecommerce DB
SHOW TABLES FROM ecommerce LIKE 'cust%'; -- pattern filter
SHOW TABLES FROM ecommerce WHERE name LIKE 'order%' LIMIT 5;

Common Mistakes

Omitting the database qualifier: Running SHOW TABLES in the wrong database shows an empty list. Always add FROM ecommerce (or switch database) to avoid confusion.
Using LIKE for complex filters: LIKE supports only simple patterns. For exclusions or multiple conditions, switch to WHERE so you can use logical operators and functions.