Mysql – How to get thesql view definitions

MySQLview

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?

Best Answer

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.

Related Question