Categories:: Table, View, & Sequence DDL

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 [ <db_name> ] | [ SCHEMA ] [ <schema_name> ] } ]

Parameters¶

LIKE 'pattern'

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%' ...

IN ACCOUNT | [ DATABASE ] db_name | [ SCHEMA ] schema_name

Specifies the scope of the command, which determines whether the command lists records only for the current/specified database or schema, or across your entire account:

The DATABASE or SCHEMA keyword is not required; you can set the scope by specifying only the database or schema name. Likewise, the database or schema name is not required if the session currently has a database in use:

If DATABASE or SCHEMA is specified without a name and the session does not currently have a database in use, the parameter has no effect on the output.
If SCHEMA is specified with a name and the session does not currently have a database in use, the schema name must be fully qualified with the database name (e.g. testdb.testschema).

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

Database: DATABASE is the default (i.e. the command returns the objects you have privileges to view in the database).
No database: ACCOUNT is the default (i.e. the command returns the objects you have privileges to view in your account).

Usage Notes¶

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: last refresh time.
- compacted_on: last compaction time.
- 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’s output provides column properties and metadata in the following columns:

| 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 |

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 at which the materialized view was last refreshed.
`compacted_on`	The timestamp at which the materialized view was last compacted.
`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.

Examples¶

Show all materialized views:

SHOW MATERIALIZED VIEWS;

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 |
|-------------------------------+------+----------+---------------+-------------+------------+------+-------+----------------------+--------------------+-------------------+-------------------------------+--------------+----------+---------+----------------+-----------+---------+--------------------------------------------+-----------|----------------------+
| 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                  |
|                               |      |          |               |             |            |      |       |                      |                    |                   |                               |              |          |         |                |           |         |       SELECT ID, price FROM inventory;     |           |                      |
+-------------------------------+------+----------+---------------+-------------+------------+------+-------+----------------------+--------------------+-------------------+-------------------------------+--------------+----------+---------+----------------+-----------+---------+--------------------------------------------+-----------+----------------------+