Description

This command is used to view all materialized views under the specified database schema. A materialized view is a special database object that can pre-calculate and store query results to improve query performance. The function of this command is similar to the SQL statement SHOW TABLES, but it is more flexible, allowing users to filter based on conditions.

For more detailed information, please refer to Materialized View.

Syntax

SHOW TABLES [IN schema_name] [LIKE 'pattern' | WHERE expr]

Parameter Description

1. LIKE pattern: Optional parameter used to filter by object name. This filter uses case-insensitive pattern matching and supports SQL wildcards % (representing any number of characters) and _ (representing a single character). For example: LIKE '%testing%'. Note that this parameter cannot be used simultaneously with the WHERE condition.

2. IN schema_name: Optional parameter used to specify the schema name. With this parameter, users can view all materialized views under the specified schema.

3. WHERE expr: Optional parameter that allows users to filter based on the fields displayed by the SHOW TABLES command. This parameter provides a more flexible query method, allowing filtering based on actual needs.

Usage Example

Example 1: View all materialized views under the default schema

SHOW TABLES WHERE is_materialized_view = true;

Example 2: View all materialized views under a specified schema

SHOW TABLES IN my_schema WHERE is_materialized_view = true;

Example 3: View all materialized views containing the word "test"

SHOW TABLES LIKE '%test%' WHERE is_materialized_view = true;

Example 4: Using IN and LIKE Parameters Together

SHOW TABLES IN my_schema LIKE '%test%' WHERE is_materialized_view = true;

Notes

1. When using LIKE and WHERE parameters, please ensure the filter conditions are correct to avoid returning empty results.
2. Please choose the appropriate parameters for the query based on actual needs to improve query efficiency.