# 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

```sql
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:

```sql
SELECT * FROM deltastream.sys."relations";
```

```sh
[{"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":"user@example.com","created_at":"2025-09-08T15:54:41.085Z","updated_by_role":"sysadmin","updated_by_role_deleted":false,"updated_by":"user@example.com","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":"user@example.com","created_at":"2026-03-03T00:05:14.798Z","updated_by_role":"sysadmin","updated_by_role_deleted":false,"updated_by":"user@example.com","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:

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

Filter by database:

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

## See Also

* [System Tables Overview](https://docs.deltastream.io/reference/sql-syntax/systables)
* [LIST RELATIONS](https://docs.deltastream.io/reference/sql-syntax/command/list-relations)
* [relation\_columns](https://docs.deltastream.io/reference/sql-syntax/systables/relation-columns)