How to Drop All Tables in ClickHouse

How do I drop all tables in a ClickHouse database?

Removes every table in a ClickHouse database by dropping the database itself or iterating through system.tables to execute DROP TABLE statements.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

How can I erase every table at once in ClickHouse?

Run DROP DATABASE to delete the database and all its tables, or query system.tables to build individual DROP TABLE statements. Both methods remove data permanently, so back up first.

When should I prefer DROP DATABASE?

Use DROP DATABASE when you no longer need the schema. Its a single, atomic command, supports IF EXISTS, ON CLUSTER, and respects permissions. Recreate the database afterward if required.

Syntax

DROP DATABASE [IF EXISTS] db_name
[ON CLUSTER cluster_name]
[NO DELAY]

How to drop all tables but keep the database?

Select table names from system.tables, concatenate them into executable SQL, then run those statements. This lets you keep grants, settings, and the database object itself.

Generate DROP statements

SELECT concat(
'DROP TABLE IF EXISTS ',
name,
' ON CLUSTER default'
) AS stmt
FROM system.tables
WHERE database = 'ecommerce';

Execute generated SQL

SET allow_experimental_object_type = 1; -- enable multi-statement execution
SELECT stmt FROM (
SELECT concat('DROP TABLE IF EXISTS ', name) AS stmt
FROM system.tables WHERE database = 'ecommerce'
) FORMAT Vertical; -- copy & execute

What are best practices?

Back up data, disable replication temporarily to avoid lag, drop materialized views first, and wrap DDL in a maintenance window. Always include IF EXISTS to avoid errors.

Common mistakes

Skipping backups: Dropping tables is irreversible. Take a snapshot or use S3 backup before running the command.

Ignoring distributed tables: Dropping only local shards leaves distributed tables broken. Always drop both local and distributed versions or drop the database cluster-wide.

Can I drop tables on a cluster?

Yesa00include ON CLUSTER cluster_name. ClickHouse propagates the DROP to every shard and replica, ensuring consistent schema removal.

Does DROP TABLE free disk space immediately?

Data parts are deleted asynchronously; disk space returns after background cleanup finishes. Monitor system.mutations to see progress.

Example workflow for an ecommerce database

1a0Stop ingestion.
2a0Back up S3 storage.
3a0Run DROP DATABASE IF EXISTS ecommerce ON CLUSTER company_cluster;
4a0Verify via SHOW DATABASES. Recreate schema if needed.

Why How to Drop All Tables in ClickHouse is important

How to Drop All Tables in ClickHouse Example Usage


-- Remove every table from the ecommerce database but keep the database object
WITH (
  SELECT groupArrayConcat(
      '\n',
      concat('DROP TABLE IF EXISTS ', name)
  ) AS all_drops
  FROM system.tables 
  WHERE database = 'ecommerce'
) AS q
SELECT q AS sql_to_run; -- copy & execute result

How to Drop All Tables in ClickHouse Syntax


-- Drop the whole database and its tables
DROP DATABASE [IF EXISTS] ecommerce 
            [ON CLUSTER company_cluster]
            [NO DELAY];

-- Drop all tables individually while retaining the database
SELECT concat('DROP TABLE IF EXISTS ', name, ' ON CLUSTER company_cluster') AS stmt
FROM system.tables
WHERE database = 'ecommerce';

Common Mistakes

Running DROP DATABASE without ON CLUSTER: only local tables get dropped, leaving other shards intact. Always append ON CLUSTER when in distributed setups.
Forgetting to disable ingestion: live inserts during DROP can cause replication errors. Pause data pipelines before schema removal.