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

Parameter	Required	Description
db_name	No	The 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_name	Yes	The view name.

Output

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

The following table describes the parameters returned by this statement.

Parameter	Description
View	The view name.
Create View	The CREATE VIEW statement of the view.
character_set_client	The character set the client uses to send statements to StarRocks.
collation_connection	The 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      |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+