search
Start Trial

LIST RELATIONS

hashtag
Syntax

[ LIST | SHOW ] [ RELATIONS | STREAMS | CHANGELOGS | MATERIALIZED VIEWS ]
[ IN SCHEMA schema_name ];

hashtag
Description

This command provides a list of relation(s) defined in a database’s schema. You can also use this command to filter further, based on relation types.

Relations are listed only if the current role has USAGE privileges on the database and schema and SELECT or INSERT privilege on the relation.

hashtag
Arguments

hashtag
IN SCHEMA schema_name

Optionally, this specifies the schema name for which the relations should be listed. Provide a fully-qualified schema name of the form <database_name>.<schema_name>. The system uses the current database and schema as the default for this command.

hashtag
Examples

hashtag
List all relations in the default schema

The following provides a list of all relations that are in the scope of a user’s account and default schema (demodb.public):

demodb.public/demostore# LIST RELATIONS;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
|  Name         |  Type     |  Owner   |  State  |  Properties                              |  Created At                   |  Updated At                   |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| keyed_pv      | stream    | sysadmin | created | { "value.format": "json",                | 2024-07-12 20:43:54 +0000 UTC | 2024-07-12 20:43:54 +0000 UTC |
|               |           |          |         | "topic.partitions": 1, "key.type":       |                               |                               |
|               |           |          |         | "STRUCT\u003cuserid VARCHAR\u003e",      |                               |                               |
|               |           |          |         | "topic": "ds_pageviews", "store":        |                               |                               |
|               |           |          |         | "pub_demo_msk", "key.format": "json",    |                               |                               |
|               |           |          |         | "topic.replicas": 3 }                    |                               |                               |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| mviewstream   | materiali | sysadmin | created | { "key.type": "STRUCT\u003c\"pageid\"    | 2024-06-25 22:08:25 +0000 UTC | 2024-06-25 22:08:27 +0000 UTC |
|               | zed_view  |          |         | VARCHAR\u003e", "primary.key": "pageid"  |                               |                               |
|               |           |          |         | }                                        |                               |                               |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| pv            | stream    | sysadmin | created | { "value.format": "json", "topic":       | 2024-03-19 03:29:03 +0000 UTC | 2024-03-19 03:29:04 +0000 UTC |
|               |           |          |         | "ds_pageviews", "store": "pub_demo_msk"  |                               |                               |
|               |           |          |         | }                                        |                               |                               |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| pv_avro       | stream    | sysadmin | created | { "value.format": "avro", "key.type":    | 2024-06-11 19:47:20 +0000 UTC | 2024-06-11 19:47:21 +0000 UTC |
|               |           |          |         | "STRUCT\u003cuserid VARCHAR\u003e",      |                               |                               |
|               |           |          |         | "topic": "pageviews_avro", "store":      |                               |                               |
|               |           |          |         | "kafka_with_sr", "key.format": "avro" }  |                               |                               |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| users_log     | changelog | sysadmin | created | { "value.format": "json", "key.type":    | 2024-05-01 20:01:44 +0000 UTC | 2024-05-01 20:01:45 +0000 UTC |
|               |           |          |         | "STRUCT\u003cuserid VARCHAR\u003e",      |                               |                               |
|               |           |          |         | "topic": "ds_users", "store":            |                               |                               |
|               |           |          |         | "pub_demo_msk", "primary.key":           |                               |                               |
|               |           |          |         | "userid", "key.format": "json" }         |                               |                               |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| dbx_pv        | table     | sysadmin | errored | { "table.data.file.location":            | 2024-07-01 22:41:56 +0000 UTC | 2024-07-01 22:42:12 +0000 UTC |
|               |           |          |         | "s3://deltastream-ctan-databricks-bucket |                               |                               |
|               |           |          |         | /new_catalog/new_schema/pageviews",      |                               |                               |
|               |           |          |         | "databricks.catalog.name":               |                               |                               |
|               |           |          |         | "new_catalog", "databricks.table.name":  |                               |                               |
|               |           |          |         | "pageviews", "databricks.schema.name":   |                               |                               |
|               |           |          |         | "new_schema", "store":                   |                               |                               |
|               |           |          |         | "test_databricks_store" }                |                               |                               |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+

hashtag
List all relations in a specific schema

The following provides a list of relations within the testdb.analytics schema regardless of the default schema:

hashtag
List all streams in the default schema

The following provides a list of all streams within the scope of a user’s account and default schema:

hashtag
List all shangelogs in the default schema

The following provides a list of all changelogs within the scope of a user’s account and default schema:

hashtag
List all materialized views in the default schema

The following provides a list of all materialized views within the scope of a user’s account and default schema:

hashtag
List all tables in the default schema

The following provides a list of all tables within the scope of a user’s account and default schema:

PreviousLIST QUERIESNextLIST ROLES

Last updated