How to List Databases in ClickHouse

How do I list all databases in ClickHouse?

SHOW DATABASES returns the names of every database available in the ClickHouse server, optionally filtered by a pattern.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why would I need to list databases in ClickHouse?

Listing databases helps you verify available schemas, audit new creations, and confirm naming before running queries that reference them.

What is the simplest way to list databases?

Run SHOW DATABASES; in any SQL client connected to ClickHouse. The server returns a single column called name containing every database.

How can I filter database names by pattern?

Add a LIKE or NOT LIKE clause. This reduces noise in clusters with many test environments.

Can I query system tables instead?

Yes. SELECT name FROM system.databases; is useful inside stored queries or when additional columns (engine, UUID) are required.

What are best practices for production clusters?

Grant SHOW DATABASES only to roles that need discovery. Prefix non-production databases (e.g., dev_) so pattern filters work. Document database purpose in Confluence or Galaxy Collections.

How do I use this in ecommerce analytics?

After confirming the ecommerce database exists, switch context USE ecommerce; and query tables such as Customers, Orders, and OrderItems.

Why How to List Databases in ClickHouse is important

How to List Databases in ClickHouse Example Usage


-- List only databases starting with 'ecom'
SHOW DATABASES LIKE 'ecom%';

-- Verify ecommerce schemas before querying business tables
SELECT name
FROM system.databases
WHERE name IN ('ecommerce','ecommerce_staging');

How to List Databases in ClickHouse Syntax


SHOW DATABASES [LIKE 'pattern' | NOT LIKE 'pattern'] [IN CURRENT DATABASE]
               [FROM | IN cluster_name];

-- Alternative system table query
SELECT name
FROM system.databases
WHERE name LIKE 'pattern%';

Common Mistakes

Using MySQL syntax: Many users type <code>SHOW SCHEMAS;</code>. ClickHouse returns an error. Fix: use <code>SHOW DATABASES;</code>.
Ignoring the LIKE filter: Dumping hundreds of databases clutters output. Fix: append <code>LIKE 'prod%'</code> or query <code>system.databases</code> with WHERE.