relations

Description

Exposes metadata about all relations (streams, changelogs, tables, materialized views, and indexes) in the organization. This is the programmatic equivalent of LIST RELATIONS. Only relations on which the current role has SELECT or INSERT privileges are returned.

Syntax

SELECT * FROM deltastream.sys."relations";

Columns

Column	Type	Nullable	Description
name	VARCHAR	No	Name of the relation.
schema_name	VARCHAR	No	Schema (namespace) containing the relation.
database_name	VARCHAR	No	Database containing the relation.
fqn	VARCHAR	No	Fully qualified name of the relation, e.g. mydb.public.my_stream.
store_name	VARCHAR	Yes	Name of the backing store, if any.
relation_type	VARCHAR	No	Type of the relation. One of stream, changelog, table, materialized_view, index.
primary_key	VARCHAR	Yes	Primary key column(s) for changelogs and tables, if defined.
partition_keys	VARCHAR	Yes	Partition key column(s), if defined.
state	VARCHAR	No	Current state of the relation. One of created, errored.
error_messages	VARCHAR	Yes	Error messages if state is errored.
properties	VARCHAR	No	JSON object of relation-specific properties (e.g. store topic, value format).
description	VARCHAR	Yes	Optional description of the relation.
owner	VARCHAR	No	The role that owns this resource.
created_by_role	VARCHAR	No	The role that created this resource.
created_by_role_deleted	BOOLEAN	No	Whether the creating role has since been deleted.
created_by	VARCHAR	No	The user that created this resource.
created_at	TIMESTAMP_LTZ	No	Timestamp when this resource was created.
updated_by_role	VARCHAR	No	The role that last updated this resource.
updated_by_role_deleted	BOOLEAN	No	Whether the last updating role has since been deleted.
updated_by	VARCHAR	No	The user that last updated this resource.
updated_at	TIMESTAMP_LTZ	No	Timestamp when this resource was last updated.
organization_id	VARCHAR	No	The unique identifier of the organization this resource belongs to.

Examples

List all relations:

SELECT * FROM deltastream.sys."relations";

[{"name":"pageviews_s3","schema_name":"public","database_name":"mydb","fqn":"mydb.public.pageviews_s3","store_name":"s3iam","owner":"sysadmin","relation_type":"stream","created_by_role":"sysadmin","created_by_role_deleted":false,"created_by":"[email protected]","created_at":"2025-09-08T15:54:41.085Z","updated_by_role":"sysadmin","updated_by_role_deleted":false,"updated_by":"[email protected]","updated_at":"2025-09-08T15:54:42.391Z","primary_key":null,"partition_keys":null,"state":"created","error_messages":null,"properties":"{\"s3.uri\":\"s3://rgc-multiline-json/jsonl\",\"store\":\"s3iam\",\"topic\":\"s3://rgc-multiline-json/jsonl\",\"value.format\":\"jsonl\"}","organization_id":"00000000-0000-0000-0000-000000000001","description":null},{"name":"mcp_audit_mv9","schema_name":"public","database_name":"mview_test","fqn":"mview_test.public.mcp_audit_mv9","store_name":"materialized_view","owner":"sysadmin","relation_type":"materialized_view","created_by_role":"sysadmin","created_by_role_deleted":false,"created_by":"[email protected]","created_at":"2026-03-03T00:05:14.798Z","updated_by_role":"sysadmin","updated_by_role_deleted":false,"updated_by":"[email protected]","updated_at":"2026-03-03T00:05:18.874Z","primary_key":null,"partition_keys":null,"state":"created","error_messages":null,"properties":"{\"store\":\"materialized_view\"}","organization_id":"00000000-0000-0000-0000-000000000001","description":null}]

Filter by relation type:

SELECT name, fqn, relation_type, state FROM deltastream.sys."relations" WHERE relation_type = 'stream';

Filter by database:

SELECT name, schema_name, relation_type FROM deltastream.sys."relations" WHERE database_name = 'mydb';

See Also

• System Tables Overview

• LIST RELATIONS

• relation_columns