SHOW Command Reference

Description

List existing objects of the specified type in Singdata Lakehouse.

Syntax

SHOW <object_type_plural> [ IN <scope_object_name> ] [ LIKE '<pattern>' | WHERE <expression> ] [ LIMIT <num> ]

Parameters

Parameter	Description	Required
<object_type_plural>	The type of object to list (plural form), e.g., TABLES, SCHEMAS, etc.	Yes
IN <scope>	Limits the scope level of the objects	No
LIKE '<pattern>'	Matches object names using wildcard patterns (supports % and _)	No
WHERE <expression>	Performs complex filtering based on object properties (mutually exclusive with LIKE)	No
LIMIT <num>	Limits the number of returned results	No

Scope Specification

Object Type	Scope Format	Example
TABLE/VIEW/MATERIALIZED VIEW/SYNONYM/VOLUME/TABLE STREAM/PIPE	IN schema_name	SHOW TABLES IN sales
Jobs	IN VCLUSTER vc_name	SHOW JOBS IN VCLUSTER prod
Indexes/Columns	IN table_name	SHOW COLUMNS IN orders
SCHEMA/VCLUSTER/USERS/ROLES	IN workspace_name	SHOW SCHEMAS IN my_workspace
Partitions	IN not supported	SHOW PARTITIONS table_name
CONNECTION/SHARE/FUNCTION	Scope specification not supported	SHOW CONNECTIONS

Result Filtering

• LIKE '<pattern>': Matches object names using wildcard patterns

  SHOW TABLES LIKE 'temp%' -- matches tables starting with "temp"

• WHERE <expression>: Performs complex filtering based on object properties (supports combined queries across all fields)

  SHOW TABLES WHERE is_view = false AND table_name LIKE '%taxi%';

  Object types that support WHERE filtering: TABLE, TABLE STREAM, CONNECTION, VCLUSTER, JOB, SHARE, SYNONYM, PIPE, SCHEMA

Special Syntax

Index and column objects support using FROM instead of IN TABLE:

SHOW INDEXES FROM customers -- equivalent to SHOW INDEXES IN TABLE customers SHOW COLUMNS FROM order_details -- equivalent to SHOW COLUMNS IN TABLE order_details

Notes

1. LIKE and WHERE are mutually exclusive -- only one can be used at a time
2. Pattern matching is case-sensitive
3. The WHERE clause supports standard SQL expression syntax
4. DROP SCHEMA will cascade-delete all objects under that schema
5. CONNECTION and SHARE do not support scope specification

Syntax Reference

Users and Privileges

• SHOW USERS
• SHOW ROLES
• SHOW GRANTS (User)
• SHOW GRANTS (Role)

Schema and Catalog

• SHOW SCHEMAS
• SHOW EXTERNAL SCHEMAS
• SHOW CATALOGS

Data Objects

• SHOW TABLES
• SHOW VIEWS
• SHOW MATERIALIZED VIEWS
• SHOW DYNAMIC TABLES
• SHOW EXTERNAL TABLES
• SHOW TABLE STREAMS
• SHOW COLUMNS
• SHOW PARTITIONS

Indexes and Synonyms

• SHOW INDEX
• SHOW SYNONYMS

Pipes and Volumes

• SHOW PIPES
• SHOW VOLUMES

Compute and Jobs

• SHOW VCLUSTERS
• SHOW JOBS
• SHOW DYNAMIC TABLE REFRESH HISTORY

Connections and Shares

• SHOW CONNECTIONS
• SHOW SHARES