<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="show-create-procedure">
      </a>
      15.7.7.10 SHOW CREATE PROCEDURE Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045170908752">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa93478601"><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">PROCEDURE</span> <em class="replaceable">proc_name</em></code></pre>
  </div>
  <p>
   This statement is a MySQL extension. It returns the exact string
        that can be used to re-create the named stored procedure. A
        similar statement,
   <a class="link" href="show-create-function.html" title="15.7.7.9 SHOW CREATE FUNCTION Statement">
    <code class="literal">
     SHOW CREATE
        FUNCTION
    </code>
   </a>
   , displays information about stored functions
        (see
   <a class="xref" href="show-create-function.html" title="15.7.7.9 SHOW CREATE FUNCTION Statement">
    Section 15.7.7.9, “SHOW CREATE FUNCTION Statement”
   </a>
   ).
  </p>
  <p>
   To use either statement, you must be the user named as the
        routine
   <code class="literal">
    DEFINER
   </code>
   , have the
   <a class="link" href="privileges-provided.html#priv_show-routine">
    <code class="literal">
     SHOW_ROUTINE
    </code>
   </a>
   privilege, have the
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   privilege at the global
        level, or have the
   <a class="link" href="privileges-provided.html#priv_create-routine">
    <code class="literal">
     CREATE
        ROUTINE
    </code>
   </a>
   ,
   <a class="link" href="privileges-provided.html#priv_alter-routine">
    <code class="literal">
     ALTER ROUTINE
    </code>
   </a>
   ,
        or
   <a class="link" href="privileges-provided.html#priv_execute">
    <code class="literal">
     EXECUTE
    </code>
   </a>
   privilege granted at a
        scope that includes the routine. The value displayed for the
   <code class="literal">
    Create Procedure
   </code>
   or
   <code class="literal">
    Create
        Function
   </code>
   field is
   <code class="literal">
    NULL
   </code>
   if you have
        only
   <a class="link" href="privileges-provided.html#priv_create-routine">
    <code class="literal">
     CREATE ROUTINE
    </code>
   </a>
   ,
   <a class="link" href="privileges-provided.html#priv_alter-routine">
    <code class="literal">
     ALTER ROUTINE
    </code>
   </a>
   , or
   <a class="link" href="privileges-provided.html#priv_execute">
    <code class="literal">
     EXECUTE
    </code>
   </a>
   .
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa9381883"><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">PROCEDURE</span> test<span class="token punctuation">.</span>citycount\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>
           Procedure<span class="token punctuation">:</span> citycount
            sql_mode<span class="token punctuation">:</span> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                      NO_ZERO_IN_DATE,NO_ZERO_DATE,
                      ERROR_FOR_DIVISION_BY_ZERO,
                      NO_ENGINE_SUBSTITUTION
    Create Procedure<span class="token punctuation">:</span> CREATE DEFINER=`me`@`localhost`
                      PROCEDURE `citycount`(IN country CHAR(3), OUT cities INT)
                      BEGIN
                        SELECT COUNT(<span class="token punctuation">*</span>) INTO cities FROM world.city
                        WHERE CountryCode = country;
                      END
character_set_client<span class="token punctuation">:</span> utf8mb4
collation_connection<span class="token punctuation">:</span> utf8mb4_0900_ai_ci
  Database Collation<span class="token punctuation">:</span> utf8mb4_0900_ai_ci
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> test<span class="token punctuation">.</span>hello\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>
            Function<span class="token punctuation">:</span> hello
            sql_mode<span class="token punctuation">:</span> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                      NO_ZERO_IN_DATE,NO_ZERO_DATE,
                      ERROR_FOR_DIVISION_BY_ZERO,
                      NO_ENGINE_SUBSTITUTION
     Create Function<span class="token punctuation">:</span> CREATE DEFINER=`me`@`localhost`
                      FUNCTION `hello`(s CHAR(20))
                      RETURNS char(50) CHARSET utf8mb4
                      DETERMINISTIC
                      RETURN CONCAT('Hello, ',s,'!')
character_set_client<span class="token punctuation">:</span> utf8mb4
collation_connection<span class="token punctuation">:</span> utf8mb4_0900_ai_ci
  Database Collation<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 routine 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 routine was created.
   <code class="literal">
    Database
        Collation
   </code>
   is the collation of the database with which
        the routine is associated.
  </p>
 </div>
 <br/>
</div>
