SHOW TABLE STATUS

Retrieves metadata about tables in the current or specified schema.

Key Behavior

When no schema name is provided, the command operates on the currently active schema (determined by SEARCH_PATH).

Syntax

SHOW TABLE STATUS
    [{FROM | IN} schema_name]
    [LIKE 'pattern' | WHERE condition]

Parameters

• schema_name Optional identifier for the target schema. If omitted, defaults to the current schema.

• LIKE 'pattern' Filters results by matching the Name column against the given pattern using SQL LIKE semantics.

Result Columns

Column	Description
Name	Table or view name
Engine	Storage engine: USTORE (in-place update) or ASTORE (append-only)
Version	Reserved; always NULL
Row_format	Data layout: ROW (row-oriented) or COLUMN (column-oriented)
Rows	Approximate number of rows
Avg_row_length	Reserved; always NULL
Data_length	Total size of table data in bytes (pg_relation_size())
Max_data_length	Reserved; always NULL
Index_length	Combined size of all indexes (pg_indexes_size())
Data_free	Reserved; always NULL
Auto_increment	Last value from associated sequence, if primary key uses SERIAL
Create_time	Timestamp when the relation was created
Update_time	Timestamp of last structural or data modification
Check_time	Reserved; always NULL
Collation	Default collation for text columns
Checksum	Reserved; always NULL
Create_options	Comma-separated list of storage parameters used during creation
Comment	User-defined description attached to the table

Usage Examples

-- Create test schema and objects
CREATE SCHEMA demo_schema;
SET SEARCH_PATH = demo_schema;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(100),
    email TEXT
) WITH (ORIENTATION = ROW, STORAGE_TYPE = USTORE);

COMMENT ON TABLE users IS 'User account registry';

CREATE VIEW active_users AS SELECT * FROM users WHERE email IS NOT NULL;

CREATE TABLE sales_data (
    sale_id SERIAL,
    amount NUMERIC(12,2),
    region TEXT
) WITH (ORIENTATION = COLUMN);

-- Retrieve all table statuses in current schema
SHOW TABLE STATUS;

-- Filter by name pattern
SHOW TABLE STATUS IN demo_schema LIKE 'user%';

-- Filter by engine type using WHERE clause
SHOW TABLE STATUS FROM demo_schema WHERE Engine = 'ASTORE';