SHOW TABLES, COLUMNS, CREATE TABLE

List the tables of a database, the columns of a table, its partitions, and the full CREATE TABLE statement - also with QuestDB's tables() and table_columns().

• Category: Tables
• Keywords: show tables, list tables, show columns, describe, describe table, table columns, schema, show create table, table definition, DDL, .tables, .schema, information_schema, show partitions, partitions, QuestDB, tables(), table_columns, table_partitions, wal_tables, upsertKey, designatedTimestamp
• See also: CREATE TABLE, ALTER, RENAME and DROP TABLE, Databases and the data folder, Views, Indexes, Saved functions, Stored procedures, Triggers, Users and roles

These statements show what is in a database: which tables there are, which columns a table has, and how a table was made. They only read; they never change anything.

Other SHOW statements: SHOW DATABASES (databases), SHOW VIEWS (views), SHOW INDEXES (indexes), SHOW FUNCTIONS (saved functions), SHOW PROCEDURES (procedures), SHOW TRIGGERS (triggers), SHOW USERS (users and roles).

• Notes: KDB has no DESCRIBE. information_schema and pg_catalog can be read like tables, and psql's \d works on the PostgreSQL port. In kdb shell, .tables is the same as SHOW TABLES, and .schema t shows SHOW CREATE TABLE t.

Example: What is in the sample database (sample data: sample)

SHOW TABLES;
SHOW COLUMNS FROM trade_log;

Result:

SHOW TABLES

(6 rows)

SHOW COLUMNS FROM trade_log

(8 rows)

SHOW TABLES

Syntax:

SHOW TABLES

Lists the tables of the current database, sorted by name. Views are not in this list (use SHOW VIEWS).

• Returns: one row per table; (0 rows) for an empty database.
• Notes: the result can be used like any query result in scripts: kdb sql market "SHOW TABLES" --format csv.

Example: An app database with users, accounts and a ledger

Setup:

CREATE TABLE users (id LONG PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE);
CREATE TABLE accounts (id INT PRIMARY KEY AUTO, user_id LONG NOT NULL REFERENCES users(id), balance DECIMAL(2) NOT NULL DEFAULT 0);
CREATE TABLE ledger (time TIMESTAMP, account INT, amount DECIMAL(2)) TIMESERIES(time) PARTITION BY MONTH;
INSERT INTO users (email) VALUES ('ram@example.com'), ('sita@example.com');
INSERT INTO accounts (user_id, balance) VALUES (1, 1000.00);

SHOW TABLES;

Result:

(3 rows)

Command line: The same list from the shell (sample data: sample)

kdb shell market
> .tables

Output:

KDB 0.2.0-dev shell on market
End statements with ';'. Type .help for commands, .exit to quit.
kdb> name         | kind       | partition | columns | rows
-------------+------------+-----------+---------+-----
brokers      | relational | NONE      |       2 |    5
live_data    | timeseries | DAY       |      12 |   12
ochl_day     | timeseries | YEAR      |       7 |   57
ochl_minutes | timeseries | MONTH     |       7 |  956
securities   | relational | NONE      |       3 |    5
trade_log    | timeseries | DAY       |       8 |   32
(6 rows, 0.0 ms)
kdb>

SHOW COLUMNS FROM

Syntax:

SHOW COLUMNS FROM table

Lists the columns of one table, in table order.

• Notes: not_null is false for a PRIMARY KEY column without the words NOT NULL, even though a key can never be NULL. UNIQUE, DEFAULT, ROUND, CHECK, SORT BY and DEDUP KEY are not shown here: use SHOW CREATE TABLE. Works for tables only, not for views.

Example: The trade_log table: references and a computed column (sample data: sample)

SHOW COLUMNS FROM trade_log;

Result:

(8 rows)

Example: Forum posts that point to their parent post

Setup:

CREATE TABLE posts (id LONG PRIMARY KEY AUTO, parent_id LONG REFERENCES posts(id), author STRING NOT NULL, body STRING NOT NULL, words LONG AS (length(body)));

SHOW COLUMNS FROM posts;

Result:

(5 rows)

Example: A table that does not exist (shows an error)

SHOW COLUMNS FROM userz;

Result:

Error: table 'userz' does not exist

Example: FROM is needed (shows an error)

Setup:

CREATE TABLE users (id LONG PRIMARY KEY);

SHOW COLUMNS users;

Result:

Error: line 1, column 14: expected FROM after SHOW COLUMNS but found 'users'

SHOW CREATE TABLE

Syntax:

SHOW CREATE TABLE table

Gives the full CREATE TABLE statement of a table, with every rule KDB keeps: types, keys, defaults, ROUND, computed columns, REFERENCES, CHECK rules with their names, and the table options with their default values filled in (like PARTITION BY DAY and SORT BY (symbol, time)).

• Returns: the text you can run in another database to make the same (empty) table.
• Notes: in kdb sql and kdb shell table format, long text is cut after 48 characters. Use --format csv (or .format csv in the shell) to see all of it.

Example: The daily candles table of the sample data (sample data: sample)

SHOW CREATE TABLE ochl_day;

Result:

(1 row)

Example: Defaults filled in: PARTITION BY and SORT BY

Setup:

CREATE TABLE live_data (timestamp TIMESTAMP, symbol SYMBOL, ltp DOUBLE) TIMESERIES(timestamp);

SHOW CREATE TABLE live_data;

Result:

(1 row)

Example: An accounts table with a CHECK rule and an index

Setup:

CREATE TABLE accounts (
  id INT PRIMARY KEY AUTO,
  email STRING NOT NULL UNIQUE,
  balance DECIMAL(2) NOT NULL DEFAULT 0 CHECK (balance >= 0)
);
CREATE INDEX accounts_balance ON accounts (balance);

SHOW CREATE TABLE accounts;

Result:

(2 rows)

Command line: See the whole text in CSV format (sample data: sample)

kdb sql market "SHOW CREATE TABLE trade_log" --format csv

Output:

create_sql
"CREATE TABLE trade_log (
  date TIMESTAMP ROUND 1d,
  transaction_id LONG,
  symbol SYMBOL,
  buyer INT REFERENCES brokers(id),
  seller INT REFERENCES brokers(id),
  quantity LONG,
  rate DOUBLE,
  amount DOUBLE AS (quantity * rate)
) TIMESERIES(date) PARTITION BY DAY SORT BY (symbol, date) DEDUP KEY (date, transaction_id)"

SHOW PARTITIONS FROM

Syntax:

SHOW PARTITIONS FROM table
SELECT * FROM table_partitions('table')

Lists the partitions of one table, oldest first, with QuestDB's columns. Both forms give the same rows.

• Notes: it needs the right to read the table (the row counts and times tell about the rows).

Example: Trades split by day

Setup:

CREATE TABLE trades (time TIMESTAMP, symbol SYMBOL, price DOUBLE) TIMESERIES(time) PARTITION BY DAY;
INSERT INTO trades VALUES ('2026-09-24T05:00:00Z', 'CITY', 10), ('2026-09-24T05:03:00Z', 'CITY', 11), ('2026-09-25T05:00:00Z', 'VOLT', 50);

SHOW PARTITIONS FROM trades;

Result:

(2 rows)

QuestDB's meta functions

Apps written for QuestDB read the schema with functions in FROM. KDB has them with QuestDB 9.4.3's column names and types, so that code runs unchanged.

• designatedTimestamp is the time column of a TIMESERIES table, NULL for other tables. dedup is true when the table has a DEDUP KEY, and upsertKey marks its columns.
• indexed is true for a column with an index of its own (ALTER TABLE t ALTER COLUMN c ADD INDEX, or CREATE INDEX on that one column).
• ttlValue/ttlUnit show a TTL as QuestDB does: SET TTL 14 DAYS shows 2 WEEK; no TTL shows 0 HOUR.
• maxUncommittedRows (500000), o3MaxLag (600000000) and the symbol sizes (256) are QuestDB's defaults: KDB has no such setting per table.
• wal_tables(): a KDB commit is applied before it returns, so writerTxn always equals sequencerTxn and suspended is false. Code that waits for the two to match goes on at once.

Example: What a rebuild reads first: time column, partitions, dedup keys

Setup:

CREATE TABLE ticks (time TIMESTAMP, symbol SYMBOL, price DOUBLE) TIMESERIES(time) PARTITION BY DAY DEDUP KEY (symbol, time);

SELECT table_name, designatedTimestamp, partitionBy, dedup FROM tables() WHERE table_name = 'ticks';
SELECT "column", type, indexed, designated, upsertKey FROM table_columns('ticks');
SELECT name, suspended, writerTxn = sequencerTxn AS caught_up FROM wal_tables() WHERE name = 'ticks';

Result:

SELECT table_name, designatedTimestamp, partitionBy, dedup FROM tables() WHERE table_name = 'ticks'

(1 row)

SELECT column, type, indexed, designated, upsertKey FROM table_columns('ticks')

(3 rows)

SELECT name, suspended, writerTxn = sequencerTxn AS caught_up FROM wal_tables() WHERE name = 'ticks'

(1 row)

Example: A table that does not exist (shows an error)

SELECT * FROM table_columns('tickz');

Result:

Error: table 'tickz' does not exist