<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="sys-table-exists">
      </a>
      30.4.4.26 The table_exists() Procedure
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045061087728">
  </a>
  <a class="indexterm" name="idm46045061086256">
  </a>
  <p>
   Tests whether a given table exists as a regular table, a
   <code class="literal">
    TEMPORARY
   </code>
   table, or a view. The procedure
          returns the table type in an
   <code class="literal">
    OUT
   </code>
   parameter.
          If both a temporary and a permanent table exist with the given
          name,
   <code class="literal">
    TEMPORARY
   </code>
   is returned.
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="sys-table-exists-parameters">
       </a>
       Parameters
      </h5>
     </div>
    </div>
   </div>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        in_db VARCHAR(64)
       </code>
       : The name of the
                database in which to check for table existence.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        in_table VARCHAR(64)
       </code>
       : The name of the
                table to check the existence of.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        out_exists ENUM('', 'BASE TABLE', 'VIEW',
                'TEMPORARY')
       </code>
       : The return value. This is an
       <code class="literal">
        OUT
       </code>
       parameter, so it must be a
                variable into which the table type can be stored. When
                the procedure returns, the variable has one of the
                following values to indicate whether the table exists:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           ''
          </code>
          : The table name does not exist
                    as a base table,
          <code class="literal">
           TEMPORARY
          </code>
          table,
                    or view.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           BASE TABLE
          </code>
          : The table name exists
                    as a base (permanent) table.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           VIEW
          </code>
          : The table name exists as a
                    view.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           TEMPORARY
          </code>
          : The table name exists
                    as a
          <code class="literal">
           TEMPORARY
          </code>
          table.
         </p>
        </li>
       </ul>
      </div>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="sys-table-exists-example">
       </a>
       Example
      </h5>
     </div>
    </div>
   </div>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75778984"><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">CREATE</span> <span class="token keyword">DATABASE</span> db1<span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">USE</span> db1<span class="token punctuation">;</span>
<span class="token keyword">Database</span> <span class="token keyword">changed</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>id <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>id <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.20 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">view</span> v_t1 <span class="token keyword">AS</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.02 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TEMPORARY</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>id <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</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">CALL</span> sys<span class="token punctuation">.</span>table_exists<span class="token punctuation">(</span><span class="token string">'db1'</span><span class="token punctuation">,</span> <span class="token string">'t1'</span><span class="token punctuation">,</span> <span class="token variable">@exists</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">SELECT</span> <span class="token variable">@exists</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</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>
<span class="token output"><span class="token punctuation">|</span> @exists   <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>
<span class="token output"><span class="token punctuation">|</span> TEMPORARY <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>
<span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CALL</span> sys<span class="token punctuation">.</span>table_exists<span class="token punctuation">(</span><span class="token string">'db1'</span><span class="token punctuation">,</span> <span class="token string">'t2'</span><span class="token punctuation">,</span> <span class="token variable">@exists</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">SELECT</span> <span class="token variable">@exists</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.02 sec)</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>
<span class="token output"><span class="token punctuation">|</span> @exists    <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>
<span class="token output"><span class="token punctuation">|</span> BASE TABLE <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>
<span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CALL</span> sys<span class="token punctuation">.</span>table_exists<span class="token punctuation">(</span><span class="token string">'db1'</span><span class="token punctuation">,</span> <span class="token string">'v_t1'</span><span class="token punctuation">,</span> <span class="token variable">@exists</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">SELECT</span> <span class="token variable">@exists</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.02 sec)</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>
<span class="token output"><span class="token punctuation">|</span> @exists <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>
<span class="token output"><span class="token punctuation">|</span> VIEW    <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>
<span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CALL</span> sys<span class="token punctuation">.</span>table_exists<span class="token punctuation">(</span><span class="token string">'db1'</span><span class="token punctuation">,</span> <span class="token string">'t3'</span><span class="token punctuation">,</span> <span class="token variable">@exists</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">SELECT</span> <span class="token variable">@exists</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</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>
<span class="token output"><span class="token punctuation">|</span> @exists <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>
<span class="token output"><span class="token punctuation">|</span>         <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>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
