How to View Table Schema in ClickHouse

Galaxy Glossary

How do I view the schema of a table in ClickHouse?

Display column names, data types, and table engine details using DESCRIBE TABLE, SHOW CREATE, or system tables.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What command shows a ClickHouse table’s columns?

Run DESCRIBE TABLE customers; (alias DESC). The server returns column names, data types, default expressions, and codecs in the original order—ideal for a quick glance.

How do I view the full CREATE statement?

Use SHOW CREATE TABLE customers; to see engine, order by, partitioning, and setting clauses. Copy-paste the output to recreate the table elsewhere.

When should I query `system.columns` directly?

Querying system.columns lets you filter or aggregate metadata. Example: SELECT name, type FROM system.columns WHERE database='shop' AND table='orders';. Use it in scripts that examine many tables at once.

How do I list all tables in a database?

Execute SHOW TABLES FROM shop;. Combine with SHOW CREATE inside a loop to export every schema in the database.

Can I inspect a distributed table’s shards?

SHOW CREATE TABLE distributed_orders; reveals the underlying remote databases and sharding key, helping you troubleshoot data locality issues.

Best practices for exploring schemas

Prefix commands with the database name to avoid surprises (DESC shop.customers). Export schemas to version control using SHOW CREATE. Automate checks with system.columns.

Common mistakes and how to avoid them

Relying only on DESCRIBE misses engine settings—always cross-check with SHOW CREATE. Forgetting the database name can return the wrong table; qualify objects explicitly.

Why How to View Table Schema in ClickHouse is important

How to View Table Schema in ClickHouse Example Usage


-- Inspect the Orders table used in ecommerce analytics
SHOW CREATE TABLE shop.orders;

-- Result snippet
/*
CREATE TABLE shop.orders
(
    `id` UInt64,
    `customer_id` UInt64,
    `order_date` DateTime,
    `total_amount` Decimal(10,2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(order_date)
ORDER BY (id)
*/

How to View Table Schema in ClickHouse Syntax


-- Quick column overview
DESCRIBE TABLE [database.]table_name;
DESC TABLE shop.customers;

-- Full DDL
SHOW CREATE TABLE [database.]table_name;
SHOW CREATE TABLE shop.orders;

-- List tables/databases
SHOW TABLES [FROM database_name];
SHOW DATABASES;

-- Query metadata as a normal table
SELECT name, type, default_kind, default_expression
FROM system.columns
WHERE database = 'shop' AND table = 'orderitems';

Common Mistakes

Mistake: Using only DESCRIBE TABLE and assuming you know the primary key or engine settings. Fix: Always run SHOW CREATE TABLE to capture engine, ORDER BY, PARTITION BY, and TTL clauses.
Mistake: Forgetting to qualify the database, leading to the wrong table when multiple databases share names. Fix: Use fully-qualified names like shop.customers or set the current database explicitly with USE shop.

Frequently Asked Questions (FAQs)

Can I view the schema of a materialized view?

Yes. Run SHOW CREATE TABLE view_name; because materialized views are stored as tables internally. The output includes its SELECT query.

Is there an information_schema like in MySQL?

ClickHouse exposes metadata through the system database. Tables such as system.tables and system.columns act as an information schema.

How can I export all table schemas at once?

Iterate over SELECT name FROM system.tables WHERE database='shop' in your scripting language. For each name, execute SHOW CREATE TABLE shop.{name} and write the DDL to files.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.