SQL SHOW TABLES and DESCRIBE

Every database keeps a catalog: a record of its own tables, views, columns, and types. You can query it like any other data. That makes the first step with an unfamiliar database a question, not a guess: which tables are there, what is in them, and is price a number or text?

show tables

On Garden Shop this returns six names, from categories to suppliers, plus anything you have created this session. Views are listed alongside tables, with nothing to tell them apart.

The columns of one table

describe orders

One row per column: its name, its type, whether it allows nulls (null), and whether it is part of a key (key). Garden Shop's tables are loaded from CSV files without constraints, so every column says YES to nulls and the key column is empty. SHOW orders returns the same thing.

A table built with constraints fills in the other columns:

create or replace table order_imports (
  order_id integer primary key,
  customer_id integer not null,
  status varchar default 'new'
);

describe order_imports;

order_id shows PRI under key, both ID columns say NO to nulls, and status shows its default, 'new'. See CREATE TABLE and table constraints.

Describe a query before you run it

describe
select o.order_id, sum(oi.quantity * oi.unit_price) as revenue
from orders o
join order_items oi on oi.order_id = o.order_id
group by o.order_id

DESCRIBE also takes a whole query and returns the columns it would produce, without the rows. revenue comes out as DOUBLE. That is the quickest way to check what a CREATE TABLE AS SELECT or a view will be built with.

Profile every column at once

summarize customers

SUMMARIZE is DuckDB's one-statement profile: per column, the type, minimum and maximum, an approximate count of distinct values, the average and quartiles for numbers, the row count, and the percentage of nulls. On customers it shows straight away that a quarter of the phone numbers are missing. It takes a query as well as a table name.

Tables in other schemas

create schema if not exists reporting;

create or replace table reporting.monthly_revenue as
select date_trunc('month', o.order_date) as month,
       sum(oi.quantity * oi.unit_price) as revenue
from orders o
join order_items oi on oi.order_id = o.order_id
group by all;

show tables;

monthly_revenue is not in the list. SHOW TABLES covers the schema you are working in, which is main unless you changed it. Ask for the other schema by name, or for everything:

show tables from reporting

show all tables

SHOW ALL TABLES returns one row per table or view with its database, schema, column names, and column types. DESCRIBE needs the schema too: describe reporting.monthly_revenue. See schemas.

show databases;

select current_database(), current_schema();

The playground runs one in-memory database, named memory, with the current schema set to main.

Querying the catalog

SHOW and DESCRIBE are shortcuts. Underneath, DuckDB exposes its catalog as table functions you can filter, join, and sort like any table. Two questions they answer that the shortcuts cannot:

select table_name, column_name, data_type
from duckdb_columns()
where column_name = 'customer_id'
order by table_name

A search across every table at once: the fastest way to find join keys in a database you have not seen before.

select schema_name, table_name, estimated_size, column_count
from duckdb_tables()
order by estimated_size desc, table_name

estimated_size is DuckDB's row count estimate. It also has a sql column holding the CREATE TABLE statement that would rebuild the table.

create or replace view customer_revenue as
select o.customer_id, sum(oi.quantity * oi.unit_price) as revenue
from orders o
join order_items oi on oi.order_id = o.order_id
group by o.customer_id;

select view_name, sql
from duckdb_views()
where not internal;

select distinct function_name, description
from duckdb_functions()
where function_name like 'date%'
order by function_name

A function with several overloads appears once per overload, which is why the lookup needs distinct.

The portable version: information_schema

select column_name, data_type, is_nullable, column_default
from information_schema.columns
where table_schema = 'main'
  and table_name = 'order_imports'
order by ordinal_position

information_schema is the SQL standard's catalog: views named tables, columns, views, and more, with the same column names across engines. The query above runs on PostgreSQL, MySQL, and SQL Server once main is swapped for their schema or database name. Use it in scripts meant to move between databases, and the duckdb_* functions when you want DuckDB's extra detail.

select table_schema, table_name, table_type
from information_schema.tables
where table_schema in ('main', 'reporting')
order by table_schema, table_name

table_type reads BASE TABLE or VIEW, the difference SHOW TABLES leaves out.

What can go wrong

show columns from orders

That is MySQL. DuckDB answers Parser Error: syntax error at or near "from". The commands for listing tables differ more between databases than almost any other SQL, and several of the best-known ones are not SQL at all: \d and \dt belong to PostgreSQL's psql client, .tables and .schema to the SQLite and DuckDB command-line shells, and Oracle's DESC to SQL*Plus and SQLcl. Typed into a SQL editor, including this playground, each one is a syntax error.

describe custmers

DuckDB says Table with name custmers does not exist! and suggests customers. The same error appears for a table in another schema named without its schema, so if the suggestion has a prefix, like reporting.monthly_revenue, use it. Every other cause of this error is on Catalog Error: table does not exist.

Across databases