<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="show-create-view">
      </a>
      15.7.7.14 SHOW CREATE VIEW Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045170770096">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa45864858"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> <em class="replaceable">view_name</em></code></pre>
  </div>
  <p>
   This statement shows the
   <a class="link" href="create-view.html" title="15.1.23 CREATE VIEW Statement">
    <code class="literal">
     CREATE
        VIEW
    </code>
   </a>
   statement that creates the named view.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63683954"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> v\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
                View<span class="token punctuation">:</span> v
         Create View<span class="token punctuation">:</span> CREATE ALGORITHM=UNDEFINED
                      DEFINER=`bob`@`localhost`
                      SQL SECURITY DEFINER VIEW
                      `v` AS select 1 AS `a`,2 AS `b`
character_set_client<span class="token punctuation">:</span> utf8mb4
collation_connection<span class="token punctuation">:</span> utf8mb4_0900_ai_ci</span></code></pre>
  </div>
  <p>
   <code class="literal">
    character_set_client
   </code>
   is the session value of
        the
   <a class="link" href="server-system-variables.html#sysvar_character_set_client">
    <code class="literal">
     character_set_client
    </code>
   </a>
   system
        variable when the view was created.
   <code class="literal">
    collation_connection
   </code>
   is the session value of
        the
   <a class="link" href="server-system-variables.html#sysvar_collation_connection">
    <code class="literal">
     collation_connection
    </code>
   </a>
   system
        variable when the view was created.
  </p>
  <p>
   Use of
   <a class="link" href="show-create-view.html" title="15.7.7.14 SHOW CREATE VIEW Statement">
    <code class="literal">
     SHOW CREATE VIEW
    </code>
   </a>
   requires
        the
   <a class="link" href="privileges-provided.html#priv_show-view">
    <code class="literal">
     SHOW VIEW
    </code>
   </a>
   privilege, and the
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   privilege for the view in
        question.
  </p>
  <p>
   View information is also available from the
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   <a class="link" href="information-schema-views-table.html" title="28.3.47 The INFORMATION_SCHEMA VIEWS Table">
    <code class="literal">
     VIEWS
    </code>
   </a>
   table. See
   <a class="xref" href="information-schema-views-table.html" title="28.3.47 The INFORMATION_SCHEMA VIEWS Table">
    Section 28.3.47, “The INFORMATION_SCHEMA VIEWS Table”
   </a>
   .
  </p>
  <p>
   MySQL lets you use different
   <a class="link" href="server-system-variables.html#sysvar_sql_mode">
    <code class="literal">
     sql_mode
    </code>
   </a>
   settings to tell the
        server the type of SQL syntax to support. For example, you might
        use the
   <a class="link" href="sql-mode.html#sqlmode_ansi">
    <code class="literal">
     ANSI
    </code>
   </a>
   SQL mode to
        ensure MySQL correctly interprets the standard SQL concatenation
        operator, the double bar (
   <code class="literal">
    ||
   </code>
   ), in your
        queries. If you then create a view that concatenates items, you
        might worry that changing the
   <a class="link" href="server-system-variables.html#sysvar_sql_mode">
    <code class="literal">
     sql_mode
    </code>
   </a>
   setting to a value
        different from
   <a class="link" href="sql-mode.html#sqlmode_ansi">
    <code class="literal">
     ANSI
    </code>
   </a>
   could
        cause the view to become invalid. But this is not the case. No
        matter how you write out a view definition, MySQL always stores
        it the same way, in a canonical form. Here is an example that
        shows how the server changes a double bar concatenation operator
        to a
   <a class="link" href="string-functions.html#function_concat">
    <code class="literal">
     CONCAT()
    </code>
   </a>
   function:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3247904"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> sql_mode <span class="token operator">=</span> <span class="token string">'ANSI'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> test<span class="token punctuation">.</span>v <span class="token keyword">AS</span> <span class="token keyword">SELECT</span> <span class="token string">'a'</span> <span class="token operator">||</span> <span class="token string">'b'</span> <span class="token keyword">as</span> col1<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">VIEW</span> test<span class="token punctuation">.</span>v\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
                View<span class="token punctuation">:</span> v
         Create View<span class="token punctuation">:</span> CREATE VIEW "v" AS select concat('a','b') AS "col1"
...
</span><span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The advantage of storing a view definition in canonical form is
        that changes made later to the value of
   <a class="link" href="server-system-variables.html#sysvar_sql_mode">
    <code class="literal">
     sql_mode
    </code>
   </a>
   do not affect the
        results from the view. However an additional consequence is that
        comments prior to
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   are
        stripped from the definition by the server.
  </p>
 </div>
 <br/>
</div>
