Edit
Feedback

SHOW CREATE VIEW

Returns the CREATE statement that was used to create a given view. The CREATE VIEW statement helps you understand how the view is defined and provides a reference for you to modify or reconstruct the view. Note that the SHOW CREATE VIEW statement requires you to have the SELECT privilege on the view and the table based on which the view is created.

From v2.5.4 onwards, you can use SHOW CREATE VIEW to query the statement that is used to create a materialized view.

Syntax

SHOW CREATE VIEW [db_name.]view_name

Parameters

ParameterRequiredDescription
db_nameNoThe database name. If this parameter is not specified, the CREATE VIEW statement of a given view in your current database is returned by default.
view_nameYesThe view name.

Output

+---------+--------------+----------------------+----------------------+
| View    | Create View  | character_set_client | collation_connection |
+---------+--------------+----------------------+----------------------+

The following table describes the parameters returned by this statement.

ParameterDescription
ViewThe view name.
Create ViewThe CREATE VIEW statement of the view.
character_set_clientThe character set the client uses to send statements to StarRocks.
collation_connectionThe rules for comparing characters in a character set.

Examples

Create a table named example_table.

CREATE TABLE example_table
(
    k1 TINYINT,
    k2 DECIMAL(10, 2) DEFAULT "10.5",
    v1 CHAR(10) REPLACE,
    v2 INT SUM
)
ENGINE = olap
AGGREGATE KEY(k1, k2)
DISTRIBUTED BY HASH(k1);

Create a view named example_view based on example_table.

CREATE VIEW example_view (k1, k2, k3, v1)
AS SELECT k1, k2, k3, v1 FROM example_table;

Display the CREATE VIEW statement of example_view.

SHOW CREATE VIEW example_db.example_view;

+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View         | Create View                                                                                                                                                                                                                                                                                                                     | character_set_client | collation_connection |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| example_view | CREATE VIEW `example_view` (k1, k2, k3, v1) COMMENT "VIEW" AS SELECT `default_cluster:db1`.`example_table`.`k1` AS `k1`, `default_cluster:db1`.`example_table`.`k2` AS `k2`, `default_cluster:db1`.`example_table`.`k3` AS `k3`, `default_cluster:db1`.`example_table`.`v1` AS `v1` FROM `default_cluster:db1`.`example_table`; | utf8                 | utf8_general_ci      |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
Edit
Feedback
In this article