SHOW and DESCRIBE

SHOW and DESCRIBE report the state of the instance: its tables and their structures, its rules, and what the workload has been doing. The same catalog can be read with ordinary SELECT statements over the sys.* views.

Commands

Command	Reports
SHOW TABLES	All tables, including the sys views.
DESCRIBE <table> (DESC)	Columns and types, storage form, the next key to be issued, and keys used and remaining.
SHOW NAMESPACES	Namespaces, sys and public first.
SHOW INDEXES	Secondary indexes with their key and covered columns.
SHOW CABINS	Cabins with their status, entries, hits and misses.
SHOW FKEYS	Declared foreign keys.
SHOW ASSERTIONS	Assertions with enforcing= and counters for checks and violations.
SHOW PATTERNS	The statement shapes the database has received.
SHOW ACCESS	How each table has been accessed, per access shape, by columns and never by values.
SHOW BUDGET	Primary-key consumption per table, with warnings as a table approaches its limit.
SHOW RELAYOUT [<table>]	The physical optimizer’s report of moves it would make and what blocks them.
SHOW META	Instance details: format version, configuration, write-ahead log counters, and what the last startup’s recovery did.
SHOW PAGE <id> [VALUES]	One page’s header and slots, for debugging.

Examples

DESCRIBE tickets;

oid=4019 root_page_id=139 clustered_type=BTREE next_id=10 columns=7 ids_issued=9 ids_remaining=281474976710646 budget_used=0.000% height=1 leaves=1
pos=0 name=id type=int64 notnull=yes pk=yes autoincrement=if-omitted
pos=1 name=flight_id type=int64 notnull=yes pk=no autoincrement=no cabin=auto references=flights
pos=2 name=passenger_id type=int64 notnull=yes pk=no autoincrement=no cabin=auto references=passengers
pos=3 name=seat type=char(3) notnull=yes pk=no autoincrement=no cabin=auto
pos=4 name=baggage_kg type=int64 notnull=yes pk=no autoincrement=no cabin=auto
pos=5 name=checked_in type=bool notnull=yes pk=no autoincrement=no cabin=auto
pos=6 name=note type=varchar notnull=no pk=no autoincrement=no cabin=auto

SHOW ASSERTIONS;

assertions=2
assertion_id=1 name=seat_limit rel=tickets enforcing=1 checks=9 violations=0 reserved=9 aborted=0 def=CREATE ASSERTION seat_limit ON tickets GROUP BY (flight_id) CHECK COUNT(*) <= 4
assertion_id=2 name=baggage_cap rel=tickets enforcing=1 checks=9 violations=0 reserved=9 aborted=0 def=CREATE ASSERTION baggage_cap ON tickets GROUP BY (passenger_id) CHECK SUM(baggage_kg) <= 46

Catalog views

The catalog is also available as views:

SELECT * FROM sys.tables;
SELECT * FROM sys.columns WHERE rel_id = 4019;
SELECT * FROM sys.patterns LIMIT 20;

Views include sys.tables, sys.columns, sys.types, sys.indexes, sys.patterns, sys.access_stats, sys.cabins, sys.fkeys and sys.assertions.

• WHERE, LIMIT and OFFSET work on views. Joins, aggregates and ORDER BY do not.
• View integers are stored as uint64 and compare by value, so WHERE oid > -1 matches every row and WHERE oid = -1 matches none.
• Unlike an ordinary table, a view does not refuse a literal of the wrong type: WHERE oid = '100' matches no row instead of being refused.

Session commands

Command	Reply
PING	PONG
SYNC	Forces the log and data to disk: OK synced
STOP	Closes the connection: OK bye

Sandbox: SYNC and STOP are disabled in the browser sandbox.

See also

• ANALYZE
• SQL language overview