<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-views-table">
      </a>
      28.3.47 The INFORMATION_SCHEMA VIEWS Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045078024464">
  </a>
  <p>
   The
   <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 provides information
      about views in databases. You must have the
   <a class="link" href="privileges-provided.html#priv_show-view">
    <code class="literal">
     SHOW VIEW
    </code>
   </a>
   privilege to access this
      table.
  </p>
  <p>
   The
   <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 has these columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_CATALOG
      </code>
     </p>
     <p>
      The name of the catalog to which the view belongs. This value
          is always
      <code class="literal">
       def
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_SCHEMA
      </code>
     </p>
     <p>
      The name of the schema (database) to which the view belongs.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_NAME
      </code>
     </p>
     <p>
      The name of the view.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       VIEW_DEFINITION
      </code>
     </p>
     <p>
      The
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement that
          provides the definition of the view. This column has most of
          what you see in the
      <code class="literal">
       Create Table
      </code>
      column
          that
      <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>
      produces.
          Skip the words before
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      and skip the words
      <code class="literal">
       WITH CHECK OPTION
      </code>
      .
          Suppose that the original statement was:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45235563"><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">CREATE</span> <span class="token keyword">VIEW</span> v <span class="token keyword">AS</span>
  <span class="token keyword">SELECT</span> s2<span class="token punctuation">,</span>s1 <span class="token keyword">FROM</span> t
  <span class="token keyword">WHERE</span> s1 <span class="token operator">&gt;</span> <span class="token number">5</span>
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> s1
  <span class="token keyword">WITH</span> <span class="token keyword">CHECK</span> <span class="token keyword">OPTION</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Then the view definition looks like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa73397262"><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">SELECT</span> s2<span class="token punctuation">,</span>s1 <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> s1 <span class="token operator">&gt;</span> <span class="token number">5</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> s1</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHECK_OPTION
      </code>
     </p>
     <p>
      The value of the
      <code class="literal">
       CHECK_OPTION
      </code>
      attribute.
          The value is one of
      <code class="literal">
       NONE
      </code>
      ,
      <code class="literal">
       CASCADE
      </code>
      , or
      <code class="literal">
       LOCAL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       IS_UPDATABLE
      </code>
     </p>
     <p>
      MySQL sets a flag, called the view updatability flag, at
      <a class="link" href="create-view.html" title="15.1.23 CREATE VIEW Statement">
       <code class="literal">
        CREATE VIEW
       </code>
      </a>
      time. The flag is
          set to
      <code class="literal">
       YES
      </code>
      (true) if
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      and
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      (and similar operations)
          are legal for the view. Otherwise, the flag is set to
      <code class="literal">
       NO
      </code>
      (false). The
      <code class="literal">
       IS_UPDATABLE
      </code>
      column in the
      <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 displays the status
          of this flag. It means that the server always knows whether a
          view is updatable.
     </p>
     <p>
      If a view is not updatable, statements such
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      ,
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      , and
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      are illegal and are
          rejected. (Even if a view is updatable, it might not be
          possible to insert into it; for details, refer to
      <a class="xref" href="view-updatability.html" title="27.5.3 Updatable and Insertable Views">
       Section 27.5.3, “Updatable and Insertable Views”
      </a>
      .)
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DEFINER
      </code>
     </p>
     <p>
      The account of the user who created the view, in
      <code class="literal">
       '
       <em class="replaceable">
        <code>
         user_name
        </code>
       </em>
       '@'
       <em class="replaceable">
        <code>
         host_name
        </code>
       </em>
       '
      </code>
      format.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SECURITY_TYPE
      </code>
     </p>
     <p>
      The view
      <code class="literal">
       SQL SECURITY
      </code>
      characteristic. The
          value is one of
      <code class="literal">
       DEFINER
      </code>
      or
      <code class="literal">
       INVOKER
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHARACTER_SET_CLIENT
      </code>
     </p>
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COLLATION_CONNECTION
      </code>
     </p>
     <p>
      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>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045077968720">
   </a>
   Notes
  </h4>
  <p>
   MySQL permits 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="sa76485587"><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.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> VIEW_DEFINITION <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>VIEWS
       <span class="token keyword">WHERE</span> TABLE_SCHEMA <span class="token operator">=</span> <span class="token string">'test'</span> <span class="token operator">AND</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">=</span> <span class="token string">'v'</span><span class="token punctuation">;</span>
<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><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>
<span class="token output"><span class="token punctuation">|</span> VIEW_DEFINITION                  <span class="token punctuation">|</span></span>
<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><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>
<span class="token output"><span class="token punctuation">|</span> select concat('a','b') AS `col1` <span class="token punctuation">|</span></span>
<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><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>
<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>
