{"id":14097,"date":"2024-01-03T19:04:08","date_gmt":"2024-01-04T02:04:08","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=14097"},"modified":"2024-01-04T02:12:27","modified_gmt":"2024-01-04T09:12:27","slug":"mysql-show-create-view","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-views\/mysql-show-create-view\/","title":{"rendered":"MySQL SHOW CREATE VIEW"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL SHOW CREATE VIEW<\/code> statement to display the statement that creates a view.<\/p>\n\n\n\n

Introduction to the MySQL SHOW CREATE VIEW statement<\/h2>\n\n\n\n

The SHOW CREATE VIEW<\/code> statement allows you to display the statement that creates a view<\/a>. Here’s the basic syntax:<\/p>\n\n\n

SHOW<\/span> CREATE<\/span> VIEW<\/span> view_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the view name after the SHOW CREATE VIEW<\/code> keyword. If the view doesn’t exist, the statement will issue an error.<\/p>\n\n\n\n
First, open Command Prompt on Windows or Terminal on a Unix-like system and connect to the classicmodels<\/code> database on the MySQL server using the mysql client tool:<\/p>\n\n\n
mysql -u root -p -D classicmodels;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new view called employee_countries<\/code>:<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> employee_countries\nAS<\/span>\nSELECT<\/span> employeeNumber, firstName, lastName, country\nFROM<\/span> employees\nINNER<\/span> JOIN<\/span> offices USING<\/span> (officeCode);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, show the statement that creates the employee_countries<\/code> view:<\/p>\n\n\n
SHOW<\/span> CREATE<\/span> VIEW<\/span> employee_countries\\G<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
*************************** 1. row ***************************\n                View: employee_countries\n         Create<\/span> View<\/span>: CREATE<\/span> ALGORITHM=UNDEFINED DEFINER=`root`<\/span>@`localhost`<\/span> SQL<\/span> SECURITY<\/span> DEFINER VIEW<\/span> `employee_countries`<\/span> AS<\/span> select<\/span> `employees`<\/span>.`employeeNumber`<\/span> AS<\/span> `employeeNumber`<\/span>,`employees`<\/span>.`firstName`<\/span> AS<\/span> `firstName`<\/span>,`employees`<\/span>.`lastName`<\/span> AS<\/span> `lastName`<\/span>,`offices`<\/span>.`country`<\/span> AS<\/span> `country`<\/span> from<\/span> (`employees`<\/span> join<\/span> `offices`<\/span> on<\/span>((`employees`<\/span>.`officeCode`<\/span> = `offices`<\/span>.`officeCode`<\/span>)))\ncharacter_set_client: cp850\ncollation_connection: cp850_general_ci\n1<\/span> row<\/span> in<\/span> set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the output:<\/p>\n\n\n\n
    \n
  • View<\/code>: specifies the view name.<\/li>\n\n\n\n
  • Create View<\/code>: stores the statement that creates the employee_countries<\/code> view.<\/li>\n<\/ul>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the SHOW CREATE VIEW<\/code> statement to display the statement used to create the view.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
We’ll create a new view in the classicmodels<\/code> sample database<\/a> and use the SHOW CREATE VIEW<\/code> statement to retrieve the statement that creates the view.<\/p>\n\n\n\n