LIST RELATIONS
Syntax
[ LIST | SHOW ] [ RELATIONS | STREAMS | CHANGELOGS | MATERIALIZED VIEWS ]
[ IN SCHEMA schema_name ];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.
Arguments
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.
Examples
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" } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+List all relations in a specific schema
The following provides a list of relations within the testdb.analytics schema regardless of the default schema:
demodb.public/demostore# LIST RELATIONS IN SCHEMA testdb.analytics;
main_db.public/pub_demo_msk# LIST RELATIONS;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| Name | Type | Owner | State | Properties | Created At | Updated At |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| total_views | changelog | sysadmin | created | { "key.type": "STRUCT\u003c\"pageid\" | 2023-02-10 21:48:25 +0000 UTC | 2023-02-25 22:08:27 +0000 UTC |
| | | | | VARCHAR\u003e", "primary.key": "pageid" | | |
| | | | | } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+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:
demodb.public/demostore# LIST STREAMS;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| 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 } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| 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" } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+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:
demodb.public/demostore# LIST CHANGELOGS;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| Name | Type | Owner | State | Properties | Created At | Updated At |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| 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" } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+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:
demodb.public/demostore# LIST MATERIALIZED VIEWS;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| Name | Type | Owner | State | Properties | Created At | Updated At |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| 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" | | |
| | | | | } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+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:
demodb.public/demostore# LIST TABLES;
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+
| Name | Type | Owner | State | Properties | Created At | Updated At |
+===============+===========+==========+=========+==========================================+===============================+===============================+
| 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" } | | |
+---------------+-----------+----------+---------+------------------------------------------+-------------------------------+-------------------------------+Last updated