14

Show create view viewName does not display correctly formatted text. How can I get the correctly-formatted SQL?

For example, I created a view like create view v1 as select * from mysql.uer, but it return like this:

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER 
VIEW v1 AS 
    select 
        mysql.user.Host AS Host,
        mysql.user.User AS User,
        mysql.user.Password AS Password,
        mysql.user.Select_priv AS Select_priv,
        ... ..., 
        mysql.user.Grant_priv AS Grant_priv,
        mysql.user.References_priv AS References_priv,
        mysql.user.Index_priv AS Index_priv,
        mysql.user.Alter_priv AS Alter_priv
    from mysql.user

How can I get my original definitions?

3
  • 4
    So, it correctly returns the SQL. Where is the problem? You will not get back exactly the select * from mysql.user because the * is expanded when the view is created. Mar 23, 2015 at 10:40
  • 2
    Original definition is gone. "Live with it".
    – Rick James
    Mar 23, 2015 at 17:25
  • phpMyAdmin can get the original definition, I hope I could do same thing. In fact, I found that v1.frm contains the original definition. But in centOS 6.5, I can't use "load_file" to read the v1.frm, it return null. Mar 24, 2015 at 2:08

1 Answer 1

14
SHOW CREATE VIEW viewName

returns the SQL definition with a CREATE VIEW statement.


SELECT  VIEW_DEFINITION 
    FROM    INFORMATION_SCHEMA.VIEWS
    WHERE   TABLE_SCHEMA    = 'DATABASENAME' 
    AND     TABLE_NAME      = 'VIEWNAME';

returns only definition.

Both are correct for getting view definition.

6
  • 1
    So what does this add to the question and another already existing answer? Mar 6, 2018 at 8:09
  • @dezso it adds another command, with a different output. Sounds reasonable to me. Mar 6, 2018 at 8:57
  • @FedericoRazzoli to my untrained eyes the result is a subset (albeit schema qualified) of the already mentioned one, and it does not bring the OP closer to what they want. Mar 6, 2018 at 9:38
  • @dezso I want to clear difference between these two query. Mar 6, 2018 at 9:39
  • I believe that the best choice depends on what will process this output: a human or a program? The question doesn't specify this, so showing both options is nice. Mar 6, 2018 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.