SHOW CREATE VIEW

Use this MySQL statement to display an SQL statement that can be used to create a view like the one named. The results may be copied and used to recreate the, but either with a different name or in a different database.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SHOW CREATE VIEW view

Explanation

Use this statement to display an SQL statement that can be used to create a view like the one named. The results may be copied and used with another database. You can also copy the results and modify the name of the view so that the statement may be used to create a similar or identical view on the same database. This statement is available as of version 5.0.1 of MySQL.

Examples

SHOW CREATE VIEW student_directory G

*************************** 1. row ***************************
       View: student_directory
Create View: CREATE ALGORITHM=UNDEFINED 
DEFINER=`ulfthewolf`@`localhost` SQL SECURITY INVOKER 
VIEW `student_directory` 
AS SELECT `students`.`student_id` AS `ID`,
CONCAT(`students`.`name_first`,
convert(repeat(_utf8' ',1) using latin1),
`students`.`name_last`) AS `Name`,
`students`.`phone_home` AS `Telephone` 
FROM `students`

This view is the same one created in the example given for the examples on the page for CREATE VIEW of our MySQL documentation. Notice that the database name (personnel) has been added to the end of the view name (employee_directory).