<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-innodb-tables-table">
      </a>
      28.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045076529152">
  </a>
  <p>
   The
   <a class="link" href="information-schema-innodb-tables-table.html" title="28.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table">
    <code class="literal">
     INNODB_TABLES
    </code>
   </a>
   table provides
      metadata about
   <code class="literal">
    InnoDB
   </code>
   tables.
  </p>
  <p>
   For related usage information and examples, see
   <a class="xref" href="innodb-information-schema-system-tables.html" title="17.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables">
    Section 17.15.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”
   </a>
   .
  </p>
  <p>
   The
   <a class="link" href="information-schema-innodb-tables-table.html" title="28.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table">
    <code class="literal">
     INNODB_TABLES
    </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_ID
      </code>
     </p>
     <p>
      An identifier for the
      <code class="literal">
       InnoDB
      </code>
      table. This
          value is unique across all databases in the instance.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NAME
      </code>
     </p>
     <p>
      The name of the table, preceded by the schema (database) name
          where appropriate (for example,
      <code class="literal">
       test/t1
      </code>
      ).
          Names of databases and user tables are in the same case as
          they were originally defined, possibly influenced by the
      <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
       <code class="literal">
        lower_case_table_names
       </code>
      </a>
      setting.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FLAG
      </code>
     </p>
     <p>
      A numeric value that represents bit-level information about
          table format and storage characteristics.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       N_COLS
      </code>
     </p>
     <p>
      The number of columns in the table. The number reported
          includes three hidden columns that are created by
      <code class="literal">
       InnoDB
      </code>
      (
      <code class="literal">
       DB_ROW_ID
      </code>
      ,
      <code class="literal">
       DB_TRX_ID
      </code>
      , and
      <code class="literal">
       DB_ROLL_PTR
      </code>
      ). The number reported also
          includes
      <a class="link" href="glossary.html#glos_virtual_generated_column" title="virtual generated column">
       virtual
          generated columns
      </a>
      , if present.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SPACE
      </code>
     </p>
     <p>
      An identifier for the tablespace where the table resides. 0
          means the
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
       system
          tablespace
      </a>
      . Any other number represents either a
      <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
       file-per-table
      </a>
      tablespace or a general tablespace. This identifier stays the
          same after a
      <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
       <code class="literal">
        TRUNCATE TABLE
       </code>
      </a>
      statement. For file-per-table tablespaces, this identifier is
          unique for tables across all databases in the instance.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROW_FORMAT
      </code>
     </p>
     <p>
      The table's row format (
      <code class="literal">
       Compact
      </code>
      ,
      <code class="literal">
       Redundant
      </code>
      ,
      <code class="literal">
       Dynamic
      </code>
      , or
      <code class="literal">
       Compressed
      </code>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ZIP_PAGE_SIZE
      </code>
     </p>
     <p>
      The zip page size. Applies only to tables with a row format of
      <code class="literal">
       Compressed
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SPACE_TYPE
      </code>
     </p>
     <p>
      The type of tablespace to which the table belongs. Possible
          values include
      <code class="literal">
       System
      </code>
      for the system
          tablespace,
      <code class="literal">
       General
      </code>
      for general
          tablespaces, and
      <code class="literal">
       Single
      </code>
      for file-per-table
          tablespaces. Tables assigned to the system tablespace using
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      or
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      <code class="literal">
       TABLESPACE=innodb_system
      </code>
      have a
      <code class="literal">
       SPACE_TYPE
      </code>
      of
      <code class="literal">
       General
      </code>
      .
          For more information, see
      <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
       <code class="literal">
        CREATE
          TABLESPACE
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INSTANT_COLS
      </code>
     </p>
     <p>
      The number of columns that existed before the first instant
          column was added using
      <code class="literal">
       ALTER TABLE
          ... ADD COLUMN
      </code>
      with
      <code class="literal">
       ALGORITHM=INSTANT
      </code>
      . This column is no longer
          used.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TOTAL_ROW_VERSIONS
      </code>
     </p>
     <p>
      The number of row versions for the table. The initial value is
          0. The value is incremented by
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE ...
          ALGORITHM=INSTANT
       </code>
      </a>
      operations that add or remove
          columns. When a table with instantly added or dropped columns
          is rebuilt due to a table-rebuilding
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      or
      <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
       <code class="literal">
        OPTIMIZE TABLE
       </code>
      </a>
      operation, the
          value is reset to 0. For more information, see
      <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-column-operations" title="Column Operations">
       Column Operations
      </a>
      .
     </p>
     <p>
      The maximum
      <code class="literal">
       TOTAL_ROW_VERSIONS
      </code>
      value is 64.
          This maximum value changes from 64 to 255 as of MySQL 9.1.0.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045076472608">
   </a>
   Example
  </h4>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43452721"><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> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLES <span class="token keyword">WHERE</span> TABLE_ID <span class="token operator">=</span> <span class="token number">214</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>
          TABLE_ID<span class="token punctuation">:</span> 1064
              NAME<span class="token punctuation">:</span> test/t1
              FLAG<span class="token punctuation">:</span> 33
            N_COLS<span class="token punctuation">:</span> 6
             SPACE<span class="token punctuation">:</span> 3
        ROW_FORMAT<span class="token punctuation">:</span> Dynamic
     ZIP_PAGE_SIZE<span class="token punctuation">:</span> 0
        SPACE_TYPE<span class="token punctuation">:</span> Single
      INSTANT_COLS<span class="token punctuation">:</span> 0
TOTAL_ROW_VERSIONS<span class="token punctuation">:</span> 3</span></code></pre>
  </div>
  <h4>
   <a name="idm46045076469936">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      You must have the
      <a class="link" href="privileges-provided.html#priv_process">
       <code class="literal">
        PROCESS
       </code>
      </a>
      privilege to query this table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
       <code class="literal">
        COLUMNS
       </code>
      </a>
      table or the
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW COLUMNS
       </code>
      </a>
      statement to view
          additional information about the columns of this table,
          including data types and default values.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
