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. This command can also be used to further filter based on a Relation types.

Relations will only be listed 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. A fully qualified Schema name of the form <database_name>.<schema_name> can be provided. The current Database and Schema are used 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   |      Created at      |      Updated at       
-----------------------+------------------+----------+----------------------+-----------------------
  users_log            | Changelog        | sysadmin | 2023-01-12T20:41:00Z | 2023-01-12T20:41:00Z  
  users2468_log        | Changelog        | sysadmin | 2023-01-12T20:46:00Z | 2023-01-12T20:46:00Z  
  log_count            | Changelog        | sysadmin | 2023-01-12T20:47:28Z | 2023-01-12T20:47:28Z  
  pageviews            | Stream           | sysadmin | 2023-01-12T20:39:02Z | 2023-01-12T20:39:02Z  
  pv_user2468_logs     | Stream           | sysadmin | 2023-01-12T20:46:27Z | 2023-01-12T20:46:27Z  
  log_count_mv         | MaterializedView | sysadmin | 2023-01-18T04:02:05Z | 2023-01-18T04:02:05Z 

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;
     Name     |  Type  |  Owner   |      Created at      |      Updated at       
--------------+--------+----------+----------------------+-----------------------
  total_views | Stream | sysadmin | 2023-02-10T21:48:21Z | 2023-02-10T21:48:21Z  

List all Streams in the default Schema

The following provides a list of all Streams that are in the scope of a user’s account and default Schema:

demodb.public/demostore# LIST STREAMS;
          Name         |  Type  |  Owner   |      Created at      |      Updated at       
-----------------------+--------+----------+----------------------+-----------------------
  pageviews            | Stream | sysadmin | 2023-01-12T20:39:02Z | 2023-01-12T20:39:02Z  
  pv_user2468_logs     | Stream | sysadmin | 2023-01-12T20:46:27Z | 2023-01-12T20:46:27Z  

List all Changelogs in the default Schema

The following provides a list of all Changelogs that are in the scope of a user’s account and default Schema:

demodb.public/demostore# LIST CHANGELOGS;
       Name      |   Type    |  Owner   |      Created at      |      Updated at       
-----------------+-----------+----------+----------------------+-----------------------
  users_log      | Changelog | sysadmin | 2023-01-12T20:41:00Z | 2023-01-12T20:41:00Z  
  users2468_log  | Changelog | sysadmin | 2023-01-12T20:46:00Z | 2023-01-12T20:46:00Z  
  log_count      | Changelog | sysadmin | 2023-01-12T20:47:28Z | 2023-01-12T20:47:28Z  

List all Materialized Views in the default Schema

The following provides a list of all Materialized Views that are in the scope of a user’s account and default Schema:

demodb.public/demostore# LIST MATERIALIZED VIEWS;
          Name         |       Type       |  Owner   |      Created at      |      Updated at       
-----------------------+------------------+----------+----------------------+-----------------------
  log_count_mv         | MaterializedView | sysadmin | 2023-01-18T04:02:05Z | 2023-01-18T04:02:05Z