<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-routines-table">
      </a>
      28.3.30 The INFORMATION_SCHEMA ROUTINES Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045078892720">
  </a>
  <p>
   The
   <a class="link" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
    <code class="literal">
     ROUTINES
    </code>
   </a>
   table provides
      information about stored routines (stored procedures and stored
      functions). The
   <a class="link" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
    <code class="literal">
     ROUTINES
    </code>
   </a>
   table does
      not include built-in (native) functions or loadable functions.
  </p>
  <p>
   The
   <a class="link" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
    <code class="literal">
     ROUTINES
    </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">
       SPECIFIC_NAME
      </code>
     </p>
     <p>
      The name of the routine.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROUTINE_CATALOG
      </code>
     </p>
     <p>
      The name of the catalog to which the routine belongs. This
          value is always
      <code class="literal">
       def
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROUTINE_SCHEMA
      </code>
     </p>
     <p>
      The name of the schema (database) to which the routine
          belongs.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROUTINE_NAME
      </code>
     </p>
     <p>
      The name of the routine.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROUTINE_TYPE
      </code>
     </p>
     <p>
      <code class="literal">
       PROCEDURE
      </code>
      for stored procedures,
      <code class="literal">
       FUNCTION
      </code>
      for stored functions.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DATA_TYPE
      </code>
     </p>
     <p>
      If the routine is a stored function, the return value data
          type. If the routine is a stored procedure, this value is
          empty.
     </p>
     <p>
      The
      <code class="literal">
       DATA_TYPE
      </code>
      value is the type name only
          with no other information. The
      <code class="literal">
       DTD_IDENTIFIER
      </code>
      value contains the type name
          and possibly other information such as the precision or
          length.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHARACTER_MAXIMUM_LENGTH
      </code>
     </p>
     <p>
      For stored function string return values, the maximum length
          in characters. If the routine is a stored procedure, this
          value is
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHARACTER_OCTET_LENGTH
      </code>
     </p>
     <p>
      For stored function string return values, the maximum length
          in bytes. If the routine is a stored procedure, this value is
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NUMERIC_PRECISION
      </code>
     </p>
     <p>
      For stored function numeric return values, the numeric
          precision. If the routine is a stored procedure, this value is
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NUMERIC_SCALE
      </code>
     </p>
     <p>
      For stored function numeric return values, the numeric scale.
          If the routine is a stored procedure, this value is
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DATETIME_PRECISION
      </code>
     </p>
     <p>
      For stored function temporal return values, the fractional
          seconds precision. If the routine is a stored procedure, this
          value is
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHARACTER_SET_NAME
      </code>
     </p>
     <p>
      For stored function character string return values, the
          character set name. If the routine is a stored procedure, this
          value is
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COLLATION_NAME
      </code>
     </p>
     <p>
      For stored function character string return values, the
          collation name. If the routine is a stored procedure, this
          value is
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DTD_IDENTIFIER
      </code>
     </p>
     <p>
      If the routine is a stored function, the return value data
          type. If the routine is a stored procedure, this value is
          empty.
     </p>
     <p>
      The
      <code class="literal">
       DATA_TYPE
      </code>
      value is the type name only
          with no other information. The
      <code class="literal">
       DTD_IDENTIFIER
      </code>
      value contains the type name
          and possibly other information such as the precision or
          length.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROUTINE_BODY
      </code>
     </p>
     <p>
      The language used for the routine definition. This value is
          always
      <code class="literal">
       SQL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROUTINE_DEFINITION
      </code>
     </p>
     <p>
      The text of the SQL statement executed by the routine.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       EXTERNAL_NAME
      </code>
     </p>
     <p>
      This value is always
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       EXTERNAL_LANGUAGE
      </code>
     </p>
     <p>
      The language of the stored routine. The value is read from the
      <code class="literal">
       external_language
      </code>
      column of the
      <code class="literal">
       mysql.routines
      </code>
      data dictionary table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PARAMETER_STYLE
      </code>
     </p>
     <p>
      This value is always
      <code class="literal">
       SQL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       IS_DETERMINISTIC
      </code>
     </p>
     <p>
      <code class="literal">
       YES
      </code>
      or
      <code class="literal">
       NO
      </code>
      , depending on
          whether the routine is defined with the
      <code class="literal">
       DETERMINISTIC
      </code>
      characteristic.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SQL_DATA_ACCESS
      </code>
     </p>
     <p>
      The data access characteristic for the routine. The value is
          one of
      <code class="literal">
       CONTAINS SQL
      </code>
      ,
      <code class="literal">
       NO
          SQL
      </code>
      ,
      <code class="literal">
       READS SQL DATA
      </code>
      , or
      <code class="literal">
       MODIFIES SQL DATA
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SQL_PATH
      </code>
     </p>
     <p>
      This value is always
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SECURITY_TYPE
      </code>
     </p>
     <p>
      The routine
      <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">
       CREATED
      </code>
     </p>
     <p>
      The date and time when the routine was created. This is a
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      value.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LAST_ALTERED
      </code>
     </p>
     <p>
      The date and time when the routine was last modified. This is
          a
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      value. If the
          routine has not been modified since its creation, this value
          is the same as the
      <code class="literal">
       CREATED
      </code>
      value.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SQL_MODE
      </code>
     </p>
     <p>
      The SQL mode in effect when the routine was created or
          altered, and under which the routine executes. For the
          permitted values, see
      <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
       Section 7.1.11, “Server SQL Modes”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROUTINE_COMMENT
      </code>
     </p>
     <p>
      The text of the comment, if the routine has one. If not, this
          value is empty.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DEFINER
      </code>
     </p>
     <p>
      The account named in the
      <code class="literal">
       DEFINER
      </code>
      clause
          (often the user who created the routine), 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">
       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 routine 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 routine was created.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DATABASE_COLLATION
      </code>
     </p>
     <p>
      The collation of the database with which the routine is
          associated.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045078794016">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To see information about a routine, 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
      <code class="literal">
       ROUTINE_DEFINITION
      </code>
      column 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>
    </li>
    <li class="listitem">
     <p>
      Information about stored function return values is also
          available in the
      <a class="link" href="information-schema-parameters-table.html" title="28.3.20 The INFORMATION_SCHEMA PARAMETERS Table">
       <code class="literal">
        PARAMETERS
       </code>
      </a>
      table. The return value row for a stored function can be
          identified as the row that has an
      <code class="literal">
       ORDINAL_POSITION
      </code>
      value of 0.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
