Metadata and introspection

Metadata respects workspace permissions. A row is visible only when the current principal can discover the corresponding object.

SHOW and DESCRIBE

Statement	Purpose
`SHOW DATABASES`	List attached catalogs/databases visible to the session.
`SHOW SCHEMAS`	List schemas in the selected catalog.
`SHOW TABLES`	List tables in the current schema.
`SHOW ALL TABLES`	List visible tables across attached namespaces.
`SHOW SECRETS`	Return redacted secret metadata; never returns credential values.
`DESCRIBE table_name`	Return bound column names, types, nullability and available defaults.
`DESCRIBE SELECT ...`	Return the result schema without consuming the result rows.
`SUMMARIZE table_or_query`	Return bounded descriptive statistics by column.

USE lakehouse.sales;
SHOW TABLES;
DESCRIBE orders;
DESCRIBE SELECT customer_id, sum(net_amount) AS revenue FROM orders GROUP BY 1;

Information Schema

Use information_schema for portable catalog discovery.

View	Important columns
`information_schema.schemata`	`catalog_name`, `schema_name`, ownership metadata where exposed
`information_schema.tables`	catalog, schema, table name and table type
`information_schema.columns`	ordinal, name, nullable flag, data type and declared numeric/text attributes
`information_schema.views`	view identity and visible definition metadata
`information_schema.table_constraints`	constraint name and type
`information_schema.key_column_usage`	constrained column ordinals
`information_schema.referential_constraints`	visible foreign-key relationships
`information_schema.routines`	function name, kind, parameters and return type available to the warehouse
`information_schema.parameters`	routine parameter order, mode and data type

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

SELECT routine_schema, routine_name, data_type
FROM information_schema.routines
WHERE lower(routine_name) = 'array_cosine_similarity';

Query inspection

EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...;

EXPLAIN returns the admitted physical plan, including distributed stages and data exchanges. EXPLAIN ANALYZE executes the query and adds observed metrics. It is not a dry run and can consume the same data and compute as the underlying statement.

Plans are diagnostic output, not a stable API. Automation should use query-history fields rather than parsing formatted plan text.

Result schemas

Applications should obtain result-column metadata through the PostgreSQL driver after prepare/describe. Names and types are fixed before execution. Dynamic-schema constructs such as PIVOT must resolve a bounded output schema during planning before the client receives row data.

Nested values whose PostgreSQL protocol has no native representation are returned as canonical JSONB. Cast explicitly when a client requires a stable scalar representation.

Visibility and consistency

Metadata queries observe the catalog snapshot established for the statement or transaction. After an administrator changes a catalog registration, reconnect or reattach it before assuming that an existing session has refreshed registration metadata.