7

How can I see the original MySQL used to create a view in phpMyAdmin or other program?

I am using phpMyAdmin version 3.3.9.

This post tells how to see the SQL used to create a view but its not the original SQL used. How can I edit a view using phpMyAdmin 3.2.4?

The code returned works, it just doesn't have my original format making it harder to edit. Is there a program to make this easier or do I need to save my original SQL in a text file somewhere?

Example:

SQL Used:

CREATE VIEW `wheel`.`new_view` AS
SELECT
`t_ci_sessions`.`session_id`,
`t_ci_sessions`.`ip_address`,
`t_ci_sessions`.`user_agent`,
`t_ci_sessions`.`last_activity`,
`t_ci_sessions`.`user_data`
FROM `wheel`.`t_ci_sessions`;
SELECT * FROM `wheel`.`ci_sessions`;

SQL phpMyAdmin Returns:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `new_view` AS select `t_ci_sessions`.`session_id` AS `session_id`,`t_ci_sessions`.`ip_address` AS `ip_address`,`t_ci_sessions`.`user_agent` AS `user_agent`,`t_ci_sessions`.`last_activity` AS `last_activity`,`t_ci_sessions`.`user_data` AS `user_data` from `t_ci_sessions`
Community
  • 1
  • 1
zechdc
  • 3,374
  • 9
  • 40
  • 52
  • similar one http://stackoverflow.com/questions/2279240/how-can-i-edit-a-view-using-phpmyadmin-3-2-4 – Pramendra Gupta Sep 09 '11 at 02:07
  • It is kinda hidden, but I did post that link in my description. I was wondering if there was a way to view the Original Formatted SQL used to create the view. – zechdc Sep 09 '11 at 02:11

2 Answers2

10

You can't find out the exact SQL used to create a view, but you can see the parsed version of it, which will of course be very close and semantically exactly the same, using this command:

show create view my_view_name;
Bohemian
  • 412,405
  • 93
  • 575
  • 722
0

Information schema have the definition of the views:

SELECT VIEW_DEFINITION 
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'viewName'
Pablo Salazar
  • 800
  • 10
  • 17