SHOW MATERIALIZED VIEWS

Lists the materialized views that you have privileges to access.

For more information about materialized views, see Working with Materialized Views.

See also:

CREATE MATERIALIZED VIEW , ALTER MATERIALIZED VIEW , DROP MATERIALIZED VIEW , DESCRIBE MATERIALIZED VIEW

Syntax

SHOW MATERIALIZED VIEWS [ LIKE '<pattern>' ]
                        [ IN
                             {
                               ACCOUNT                  |

                               DATABASE                 |
                               DATABASE <database_name> |

                               SCHEMA                   |
                               SCHEMA <schema_name>     |
                               <schema_name>
                             }
                        ]
Copy

Parameters

LIKE 'pattern'

Optionally filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (% and _).

For example, the following patterns return the same results:

... LIKE '%testing%' ...
... LIKE '%TESTING%' ...

. Default: No value (no filtering is applied to the output).

[ IN ... ]

Optionally specifies the scope of the command. Specify one of the following:

ACCOUNT

Returns records for the entire account.

DATABASE, . DATABASE db_name

Returns records for the current database in use or for a specified database (db_name).

If you specify DATABASE without db_name and no database is in use, the keyword has no effect on the output.

SCHEMA, . SCHEMA schema_name, . schema_name

Returns records for the current schema in use or a specified schema (schema_name).

SCHEMA is optional if a database is in use or if you specify the fully qualified schema_name (for example, db.schema).

If no database is in use, specifying SCHEMA has no effect on the output.

Default: Depends on whether the session currently has a database in use:

  • Database: DATABASE is the default (that is, the command returns the objects you have privileges to view in the database).

  • No database: ACCOUNT is the default (that is, the command returns the objects you have privileges to view in your account).

Usage Notes

  • Columns that start with the prefix is_ return either Y (yes) or N (no).

  • The command does not require a running warehouse to execute.

  • The command returns a maximum of 10K records for the specified object type, as dictated by the access privileges for the role used to execute the command; any records above the 10K limit are not returned, even with a filter applied.

    To view results for which more than 10K records exist, query the corresponding view (if one exists) in the Snowflake Information Schema.

  • To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.

  • The output columns are similar to the output columns for SHOW TABLES, but includes the following additional columns:

    • refreshed_on: time of the last DML operation on the base table that was processed by a “refresh” operation.

    • compacted_on: time of the last DML operation on the base table that was processed by a “compaction” operation.

    • behind_by: If the background process that updates the materialized view with changes from the base table has not yet brought the materialized view up to date, then this column shows approximately how many seconds the materialized view is “behind” the base table. Note that even if this shows that the materialized view is not up to date, any queries on the materialized view will still return up-to-date results (they just might take a little longer as extra information is retrieved from the base table).

  • The command SHOW VIEWS also shows information about materialized views.

Output

The command output provides materialized view properties and metadata in the following columns:

Column

Description

created_on

The timestamp at which the materialized view was created.

name

The name of the materialized view.

reserved

Reserved for future use.

database_name

The name of the database in which the materialized view exists.

schema_name

The name of the schema in which the materialized view exists.

cluster_by

Information about the clustering columns (if the materialized view is clustered).

rows

The number of rows in the materialized view.

bytes

The number of bytes of data in the materialized view.

source_database_name

The name of the database in which the materialized view’s base table exists.

source_schema_name

The name of the schema in which the materialized view’s base table exists.

source_table_name

The name of the materialized view’s base table.

refreshed_on

The timestamp of the last DML operation on the base table that was processed by a “refresh” operation.

compacted_on

The timestamp of the last DML operation on the base table that was processed by a “compaction” operation.

owner

The owner of the materialized view.

invalid

True if the materialized view is currently invalid (for example, if the base table dropped a column that the view used); false otherwise.

invalid_reason

The reason (if any) that the materialized view is currently invalid.

behind_by

How far the updates of the materialized view are behind the updates of the base table.

comment

Optional comment.

text

The text of the command that created this materialized view (e.g. CREATE MATERIALIZED VIEW …).

is_secure

True if the materialized view is a secure view; false otherwise.

automatic_clustering

True if the view is clustered and the clustering is automatic.

owner_role_type

The type of role that owns the object, either ROLE or DATABASE_ROLE. . If a Snowflake Native App owns the object, the value is APPLICATION. . Snowflake returns NULL if you delete the object because a deleted object does not have an owner role.

budget

Name of the budget if the object is monitored by a budget. NULL otherwise.

owner_role_type

The type of role that owns the object, either ROLE or DATABASE_ROLE. . If a Snowflake Native App owns the object, the value is APPLICATION. . Snowflake returns NULL if you delete the object because a deleted object does not have an owner role.

Examples

Show all materialized views:

SHOW MATERIALIZED VIEWS;
Copy

Show only materialized views with names matching the specified regular expression:

SHOW MATERIALIZED VIEWS LIKE 'mv1%';

+-------------------------------+------+----------+---------------+-------------+------------+------+-------+----------------------+--------------------+-------------------+-------------------------------+--------------+----------+---------+----------------+-----------+---------+--------------------------------------------+-----------+----------------------+-----------------+----------+
| created_on                    | name | reserved | database_name | schema_name | cluster_by | rows | bytes | source_database_name | source_schema_name | source_table_name | refreshed_on                  | compacted_on | owner    | invalid | invalid_reason | behind_by | comment | text                                       | is_secure | automatic_clustering | owner_role_type | budget   |
|-------------------------------+------+----------+---------------+-------------+------------+------+-------+----------------------+--------------------+-------------------+-------------------------------+--------------+----------+---------+----------------+-----------+---------+--------------------------------------------+-----------|----------------------+-----------------+----------|
| 2018-10-05 17:13:17.579 -0700 | MV1  |          | TEST_DB1      | PUBLIC      |            |    0 |     0 | TEST_DB1             | PUBLIC             | INVENTORY         | 2018-10-05 17:13:50.373 -0700 | NULL         | SYSADMIN | false   | NULL           | 0s        |         | CREATE OR REPLACE MATERIALIZED VIEW mv1 AS | false     | OFF                  | ROLE            | MYBUDGET |
|                               |      |          |               |             |            |      |       |                      |                    |                   |                               |              |          |         |                |           |         |       SELECT ID, price FROM inventory;     |           |                      |                 |          |
+-------------------------------+------+----------+---------------+-------------+------------+------+-------+----------------------+--------------------+-------------------+-------------------------------+--------------+----------+---------+----------------+-----------+---------+--------------------------------------------+-----------+----------------------+-----------------+----------+
Copy