MySQL SHOW CREATE VIEW

Summary: in this tutorial, you will learn how to use the MySQL SHOW CREATE VIEW statement to display the statement that creates a view.

Introduction to the MySQL SHOW CREATE VIEW statement

The SHOW CREATE VIEW statement allows you to display the statement that creates a view. Here’s the basic syntax:

SHOW CREATE VIEW view_name;Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the view name after the SHOW CREATE VIEW keyword. If the view doesn’t exist, the statement will issue an error.

We’ll create a new view in the classicmodels sample database and use the SHOW CREATE VIEW statement to retrieve the statement that creates the view.

First, open Command Prompt on Windows or Terminal on a Unix-like system and connect to the classicmodels database on the MySQL server using the mysql client tool:

mysql -u root -p -D classicmodels;Code language: SQL (Structured Query Language) (sql)

Second, create a new view called employee_countries:

CREATE VIEW employee_countries
AS
SELECT employeeNumber, firstName, lastName, country
FROM employees
INNER JOIN offices USING (officeCode);Code language: SQL (Structured Query Language) (sql)

Third, show the statement that creates the employee_countries view:

SHOW CREATE VIEW employee_countries\GCode language: SQL (Structured Query Language) (sql)

Output:

*************************** 1. row ***************************
                View: employee_countries
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `employee_countries` AS select `employees`.`employeeNumber` AS `employeeNumber`,`employees`.`firstName` AS `firstName`,`employees`.`lastName` AS `lastName`,`offices`.`country` AS `country` from (`employees` join `offices` on((`employees`.`officeCode` = `offices`.`officeCode`)))
character_set_client: cp850
collation_connection: cp850_general_ci
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In the output:

• View: specifies the view name.
• Create View: stores the statement that creates the employee_countries view.

Summary

• Use the SHOW CREATE VIEW statement to display the statement used to create the view.