<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-information-schema-files-table">
      </a>
      17.15.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES
     </h3>
    </div>
   </div>
  </div>
  <p>
   The Information Schema
   <a class="link" href="information-schema-files-table.html" title="28.3.15 The INFORMATION_SCHEMA FILES Table">
    <code class="literal">
     FILES
    </code>
   </a>
   table
      provides metadata about all
   <code class="literal">
    InnoDB
   </code>
   tablespace
      types including
   <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
    file-per-table
      tablespaces
   </a>
   ,
   <a class="link" href="glossary.html#glos_general_tablespace" title="general tablespace">
    general
      tablespaces
   </a>
   , the
   <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
    system tablespace
   </a>
   ,
   <a class="link" href="glossary.html#glos_temporary_tablespace" title="temporary tablespace">
    temporary table
      tablespaces
   </a>
   , and
   <a class="link" href="glossary.html#glos_undo_tablespace" title="undo tablespace">
    undo
      tablespaces
   </a>
   (if present).
  </p>
  <p>
   This section provides
   <code class="literal">
    InnoDB
   </code>
   -specific usage
      examples. For more information about data provided by the
      Information Schema
   <code class="literal">
    FILES
   </code>
   table, see
   <a class="xref" href="information-schema-files-table.html" title="28.3.15 The INFORMATION_SCHEMA FILES Table">
    Section 28.3.15, “The INFORMATION_SCHEMA FILES Table”
   </a>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The
    <a class="link" href="information-schema-innodb-tablespaces-table.html" title="28.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table">
     <code class="literal">
      INNODB_TABLESPACES
     </code>
    </a>
    and
    <a class="link" href="information-schema-innodb-datafiles-table.html" title="28.4.10 The INFORMATION_SCHEMA INNODB_DATAFILES Table">
     <code class="literal">
      INNODB_DATAFILES
     </code>
    </a>
    tables also
        provide metadata about
    <code class="literal">
     InnoDB
    </code>
    tablespaces,
        but data is limited to file-per-table, general, and undo
        tablespaces.
   </p>
  </div>
  <p>
   This query retrieves metadata about the
   <code class="literal">
    InnoDB
   </code>
   system tablespace from fields of the Information Schema
   <a class="link" href="information-schema-files-table.html" title="28.3.15 The INFORMATION_SCHEMA FILES Table">
    <code class="literal">
     FILES
    </code>
   </a>
   table that are pertinent to
   <code class="literal">
    InnoDB
   </code>
   tablespaces.
   <a class="link" href="information-schema-files-table.html" title="28.3.15 The INFORMATION_SCHEMA FILES Table">
    <code class="literal">
     FILES
    </code>
   </a>
   columns that are not relevant
      to
   <code class="literal">
    InnoDB
   </code>
   always return
   <code class="literal">
    NULL
   </code>
   , and are excluded from the query.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa83907557"><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">SELECT</span> FILE_ID<span class="token punctuation">,</span> FILE_NAME<span class="token punctuation">,</span> FILE_TYPE<span class="token punctuation">,</span> TABLESPACE_NAME<span class="token punctuation">,</span> FREE_EXTENTS<span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     TOTAL_EXTENTS<span class="token punctuation">,</span>  <span class="token keyword">EXTENT_SIZE</span><span class="token punctuation">,</span> <span class="token keyword">INITIAL_SIZE</span><span class="token punctuation">,</span> MAXIMUM_SIZE<span class="token punctuation">,</span> <span class="token keyword">AUTOEXTEND_SIZE</span><span class="token punctuation">,</span> DATA_FREE<span class="token punctuation">,</span> <span class="token keyword">STATUS</span> <span class="token keyword">ENGINE</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>FILES <span class="token keyword">WHERE</span> TABLESPACE_NAME <span class="token operator">LIKE</span> <span class="token string">'innodb_system'</span> \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>
        FILE_ID<span class="token punctuation">:</span> 0
      FILE_NAME<span class="token punctuation">:</span> ./ibdata1
      FILE_TYPE<span class="token punctuation">:</span> TABLESPACE
TABLESPACE_NAME<span class="token punctuation">:</span> innodb_system
   FREE_EXTENTS<span class="token punctuation">:</span> 0
  TOTAL_EXTENTS<span class="token punctuation">:</span> 12
    EXTENT_SIZE<span class="token punctuation">:</span> 1048576
   INITIAL_SIZE<span class="token punctuation">:</span> 12582912
   MAXIMUM_SIZE<span class="token punctuation">:</span> NULL
AUTOEXTEND_SIZE<span class="token punctuation">:</span> 67108864
      DATA_FREE<span class="token punctuation">:</span> 4194304
         ENGINE<span class="token punctuation">:</span> NORMAL</span></code></pre>
  </div>
  <p>
   This query retrieves the
   <code class="literal">
    FILE_ID
   </code>
   (equivalent to
      the space ID) and the
   <code class="literal">
    FILE_NAME
   </code>
   (which includes
      path information) for
   <code class="literal">
    InnoDB
   </code>
   file-per-table and
      general tablespaces. File-per-table and general tablespaces have a
   <code class="filename">
    .ibd
   </code>
   file extension.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa83793155"><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">SELECT</span> FILE_ID<span class="token punctuation">,</span> FILE_NAME <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>FILES
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> FILE_NAME <span class="token operator">LIKE</span> <span class="token string">'%.ibd%'</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> FILE_ID<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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> FILE_ID <span class="token punctuation">|</span> FILE_NAME                             <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>       2 <span class="token punctuation">|</span> ./mysql/plugin.ibd                    <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>       3 <span class="token punctuation">|</span> ./mysql/servers.ibd                   <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>       4 <span class="token punctuation">|</span> ./mysql/help_topic.ibd                <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>       5 <span class="token punctuation">|</span> ./mysql/help_category.ibd             <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>       6 <span class="token punctuation">|</span> ./mysql/help_relation.ibd             <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>       7 <span class="token punctuation">|</span> ./mysql/help_keyword.ibd              <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>       8 <span class="token punctuation">|</span> ./mysql/time_zone_name.ibd            <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>       9 <span class="token punctuation">|</span> ./mysql/time_zone.ibd                 <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      10 <span class="token punctuation">|</span> ./mysql/time_zone_transition.ibd      <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      11 <span class="token punctuation">|</span> ./mysql/time_zone_transition_type.ibd <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      12 <span class="token punctuation">|</span> ./mysql/time_zone_leap_second.ibd     <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      13 <span class="token punctuation">|</span> ./mysql/innodb_table_stats.ibd        <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      14 <span class="token punctuation">|</span> ./mysql/innodb_index_stats.ibd        <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      15 <span class="token punctuation">|</span> ./mysql/slave_relay_log_info.ibd      <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      16 <span class="token punctuation">|</span> ./mysql/slave_master_info.ibd         <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      17 <span class="token punctuation">|</span> ./mysql/slave_worker_info.ibd         <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      18 <span class="token punctuation">|</span> ./mysql/gtid_executed.ibd             <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      19 <span class="token punctuation">|</span> ./mysql/server_cost.ibd               <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      20 <span class="token punctuation">|</span> ./mysql/engine_cost.ibd               <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      21 <span class="token punctuation">|</span> ./sys/sys_config.ibd                  <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      23 <span class="token punctuation">|</span> ./test/t1.ibd                         <span class="token punctuation">|</span></span>
<span class="token output">    <span class="token punctuation">|</span>      26 <span class="token punctuation">|</span> /home/user/test/test/t2.ibd           <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   This query retrieves the
   <code class="literal">
    FILE_ID
   </code>
   and
   <code class="literal">
    FILE_NAME
   </code>
   for the
   <code class="literal">
    InnoDB
   </code>
   global temporary tablespace. Global temporary tablespace file
      names are prefixed by
   <code class="literal">
    ibtmp
   </code>
   .
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45010763"><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">SELECT</span> FILE_ID<span class="token punctuation">,</span> FILE_NAME <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>FILES
       <span class="token keyword">WHERE</span> FILE_NAME <span class="token operator">LIKE</span> <span class="token string">'%ibtmp%'</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>
<span class="token output"><span class="token punctuation">|</span> FILE_ID <span class="token punctuation">|</span> FILE_NAME <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>
<span class="token output"><span class="token punctuation">|</span>      22 <span class="token punctuation">|</span> ./ibtmp1  <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></code></pre>
  </div>
  <p>
   Similarly,
   <code class="literal">
    InnoDB
   </code>
   undo tablespace file names
      are prefixed by
   <code class="literal">
    undo
   </code>
   . The following query
      returns the
   <code class="literal">
    FILE_ID
   </code>
   and
   <code class="literal">
    FILE_NAME
   </code>
   for
   <code class="literal">
    InnoDB
   </code>
   undo
      tablespaces.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa98027651"><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">SELECT</span> FILE_ID<span class="token punctuation">,</span> FILE_NAME <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>FILES
       <span class="token keyword">WHERE</span> FILE_NAME <span class="token operator">LIKE</span> <span class="token string">'%undo%'</span><span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
