MySQL - SHOW CREATE VIEW Statement

---

---

MySQL SHOW CREATE VIEW Statement

A MySQL view is a composition of a table in the form of a predefined SQL query. It is stored in the database with an associated name.

The MySQL SHOW CREATE VIEW Statement displays the CREATE VIEW statement used to create the specified view.

Syntax

Following is the syntax of the ALTER VIEW Statement

SHOW CREATE VIEW name

Where, name is the name of the view for which you need the CREATE statement.

Example

Suppose we have created a table named dispatches_data using the CREATE statement as shown below −

CREATE TABLE dispatches_data(
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchTimeStamp timestamp,
   Price INT,
   Location VARCHAR(255)
);

Now, Let us insert some records into the dispatches_data table −

Insert into dispatches_data values
('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 
'Hyderabad'),
('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 
'Vishakhapatnam'),
('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 
'Vijayawada'),
('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 
'Chennai'),
('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 
'Goa');

Assume we have created a view using the CREATE VIEW statement as shown below −

CREATE VIEW testView AS SELECT * FROM dispatches_data;

You can retrieve the definition of the above created view using the SHOW CREATE VIEW statement as shown below −

SHOW CREATE VIEW testView\G;

Output

The above mysql query produces the following output −

************** 1. row **************
                View: testview
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
        		      SQL SECURITY DEFINER VIEW `testview` AS select 
					  `dispatches_data`.`ProductName` AS `ProductName`,
					  `dispatches_data`.`CustomerName` AS `CustomerName`,
					  `dispatches_data`.`DispatchTimeStamp` AS 
					  `DispatchTimeStamp`,`dispatches_data`.`Price` AS 
					  `Price`,`dispatches_data`.`Location` AS `Location` 
					  from `dispatches_data`
character_set_client: cp850
collation_connection: cp850_general_ci

Following query alters the ALGORITHM of the table −

ALTER ALGORITHM=MERGE VIEW testView AS SELECT * FROM dispatches_data;

If you retrieve the definition of the above created view after altering the table you can observe the name of the algorithm −

SHOW CREATE VIEW testView\G;

Output

After executing the above query, it generates the following output −

************** 1. row **************
                View: testview
         Create View: CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` 
		              SQL SECURITY DEFINER VIEW `testview` AS select 
					  `dispatches_data`.`ProductName` AS `ProductName`,
					  `dispatches_data`.`CustomerName` AS `CustomerName`,
					  `dispatches_data`.`DispatchTimeStamp` AS 
					  `DispatchTimeStamp`,`dispatches_data`.`Price` AS 
					  `Price`,`dispatches_data`.`Location` AS `Location` 
					  from `dispatches_data`
character_set_client: cp850
collation_connection: cp850_general_ci