6

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
    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. – ypercubeᵀᴹ Mar 23 '15 at 10:40
  • 1
    Original definition is gone. "Live with it". – Rick James Mar 23 '15 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. – user2400825 Mar 24 '15 at 2:08
2
Show create view 'viewName'

returns the SQL definition with create view statement while

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.

  • 1
    So what does this add to the question and another already existing answer? – dezso Mar 6 '18 at 8:09
  • @dezso it adds another command, with a different output. Sounds reasonable to me. – Federico Razzoli Mar 6 '18 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. – dezso Mar 6 '18 at 9:38
  • @dezso I want to clear difference between these two query. – Pankaj Kumar Mar 6 '18 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. – Federico Razzoli Mar 6 '18 at 9:54
-1

Best solution I have found is:

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

Misread question. This gives the same format as SHOW CREATE VIEW VIEWNAME; as posted by OP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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