<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="show-table-status">
      </a>
      15.7.7.38 SHOW TABLE STATUS Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045169287984">
  </a>
  <a class="indexterm" name="idm46045169286928">
  </a>
  <a class="indexterm" name="idm46045169285440">
  </a>
  <a class="indexterm" name="idm46045169283952">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92183339"><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">TABLE</span> <span class="token keyword">STATUS</span>
    <span class="token punctuation">[</span>{<span class="token keyword">FROM</span> <span class="token operator">|</span> <span class="token keyword">IN</span>} <em class="replaceable">db_name</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token operator">LIKE</span> <span class="token string">'<em class="replaceable">pattern</em>'</span> <span class="token operator">|</span> <span class="token keyword">WHERE</span> <em class="replaceable">expr</em><span class="token punctuation">]</span></code></pre>
  </div>
  <p>
   <a class="link" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
    <code class="literal">
     SHOW TABLE STATUS
    </code>
   </a>
   works like
   <a class="link" href="show-tables.html" title="15.7.7.39 SHOW TABLES Statement">
    <code class="literal">
     SHOW TABLES
    </code>
   </a>
   , but provides a lot
        of information about each non-
   <code class="literal">
    TEMPORARY
   </code>
   table. You can also get this list using the
   <a class="link" href="mysqlshow.html" title="6.5.6 mysqlshow — Display Database, Table, and Column Information">
    <span class="command">
     <strong>
      mysqlshow
        --status
      <em class="replaceable">
       <code>
        db_name
       </code>
      </em>
     </strong>
    </span>
   </a>
   command.
        The
   <a class="link" href="string-comparison-functions.html#operator_like">
    <code class="literal">
     LIKE
    </code>
   </a>
   clause, if present,
        indicates which table names to match. The
   <code class="literal">
    WHERE
   </code>
   clause can be given to select rows
        using more general conditions, as discussed in
   <a class="xref" href="extended-show.html" title="28.8 Extensions to SHOW Statements">
    Section 28.8, “Extensions to SHOW Statements”
   </a>
   .
  </p>
  <p>
   This statement also displays information about views.
  </p>
  <p>
   <a class="link" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
    <code class="literal">
     SHOW TABLE STATUS
    </code>
   </a>
   output has
        these columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       Name
      </code>
     </p>
     <p>
      The name of the table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Engine
      </code>
     </p>
     <p>
      The storage engine for the table. See
      <a class="xref" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       Chapter 17,
       <i>
        The InnoDB Storage Engine
       </i>
      </a>
      , and
      <a class="xref" href="storage-engines.html" title="Chapter 18 Alternative Storage Engines">
       Chapter 18,
       <i>
        Alternative Storage Engines
       </i>
      </a>
      .
     </p>
     <p>
      For partitioned tables,
      <code class="literal">
       Engine
      </code>
      shows the
            name of the storage engine used by all partitions.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Version
      </code>
     </p>
     <p>
      This column is unused. With the removal of
      <code class="filename">
       .frm
      </code>
      files in MySQL 8.0, this column
            now reports a hardcoded value of
      <code class="literal">
       10
      </code>
      ,
            which was the last
      <code class="filename">
       .frm
      </code>
      file version
            used in MySQL 5.7.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Row_format
      </code>
     </p>
     <p>
      The row-storage format (
      <code class="literal">
       Fixed
      </code>
      ,
      <code class="literal">
       Dynamic
      </code>
      ,
      <code class="literal">
       Compressed
      </code>
      ,
      <code class="literal">
       Redundant
      </code>
      ,
      <code class="literal">
       Compact
      </code>
      ).
            For
      <code class="literal">
       MyISAM
      </code>
      tables,
      <code class="literal">
       Dynamic
      </code>
      corresponds to what
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk -dvv
        </strong>
       </span>
      </a>
      reports as
      <code class="literal">
       Packed
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Rows
      </code>
     </p>
     <p>
      The number of rows. Some storage engines, such as
      <code class="literal">
       MyISAM
      </code>
      , store the exact count. For other
            storage engines, such as
      <code class="literal">
       InnoDB
      </code>
      , this
            value is an approximation, and may vary from the actual
            value by as much as 40% to 50%. In such cases, use
      <code class="literal">
       SELECT COUNT(*)
      </code>
      to obtain an accurate
            count.
     </p>
     <p>
      The
      <code class="literal">
       Rows
      </code>
      value is
      <code class="literal">
       NULL
      </code>
      for
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      tables.
     </p>
     <p>
      For
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      tables, the row
            count is only a rough estimate used in SQL optimization.
            (This is also true if the
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      table is partitioned.)
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Avg_row_length
      </code>
     </p>
     <p>
      The average row length.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Data_length
      </code>
     </p>
     <p>
      For
      <code class="literal">
       MyISAM
      </code>
      ,
      <code class="literal">
       Data_length
      </code>
      is the length of the data
            file, in bytes.
     </p>
     <p>
      For
      <code class="literal">
       InnoDB
      </code>
      ,
      <code class="literal">
       Data_length
      </code>
      is the approximate amount of
            space allocated for the clustered index, in bytes.
            Specifically, it is the clustered index size, in pages,
            multiplied by the
      <code class="literal">
       InnoDB
      </code>
      page size.
     </p>
     <p>
      Refer to the notes at the end of this section for
            information regarding other storage engines.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Max_data_length
      </code>
     </p>
     <p>
      For
      <code class="literal">
       MyISAM
      </code>
      ,
      <code class="literal">
       Max_data_length
      </code>
      is maximum length of the
            data file. This is the total number of bytes of data that
            can be stored in the table, given the data pointer size
            used.
     </p>
     <p>
      Unused for
      <code class="literal">
       InnoDB
      </code>
      .
     </p>
     <p>
      Refer to the notes at the end of this section for
            information regarding other storage engines.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Index_length
      </code>
     </p>
     <p>
      For
      <code class="literal">
       MyISAM
      </code>
      ,
      <code class="literal">
       Index_length
      </code>
      is the length of the index
            file, in bytes.
     </p>
     <p>
      For
      <code class="literal">
       InnoDB
      </code>
      ,
      <code class="literal">
       Index_length
      </code>
      is the approximate amount of
            space allocated for non-clustered indexes, in bytes.
            Specifically, it is the sum of non-clustered index sizes, in
            pages, multiplied by the
      <code class="literal">
       InnoDB
      </code>
      page
            size.
     </p>
     <p>
      Refer to the notes at the end of this section for
            information regarding other storage engines.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Data_free
      </code>
     </p>
     <p>
      The number of allocated but unused bytes.
     </p>
     <p>
      <code class="literal">
       InnoDB
      </code>
      tables report the free space of
            the tablespace to which the table belongs. For a table
            located in the shared tablespace, this is the free space of
            the shared tablespace. If you are using multiple tablespaces
            and the table has its own tablespace, the free space is for
            only that table. Free space means the number of bytes in
            completely free extents minus a safety margin. Even if free
            space displays as 0, it may be possible to insert rows as
            long as new extents need not be allocated.
     </p>
     <p>
      For NDB Cluster,
      <code class="literal">
       Data_free
      </code>
      shows the
            space allocated on disk for, but not used by, a Disk Data
            table or fragment on disk. (In-memory data resource usage is
            reported by the
      <code class="literal">
       Data_length
      </code>
      column.)
     </p>
     <p>
      For partitioned tables, this value is only an estimate and
            may not be absolutely correct. A more accurate method of
            obtaining this information in such cases is to query the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
       <code class="literal">
        PARTITIONS
       </code>
      </a>
      table, as shown in
            this example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88615181"><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">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>DATA_FREE<span class="token punctuation">)</span>
    <span class="token keyword">FROM</span>  INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">PARTITIONS</span>
    <span class="token keyword">WHERE</span> TABLE_SCHEMA <span class="token operator">=</span> <span class="token string">'mydb'</span>
    <span class="token operator">AND</span>   <span class="token keyword">TABLE_NAME</span>   <span class="token operator">=</span> <span class="token string">'mytable'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For more information, see
      <a class="xref" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
       Section 28.3.21, “The INFORMATION_SCHEMA PARTITIONS Table”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Auto_increment
      </code>
     </p>
     <p>
      The next
      <code class="literal">
       AUTO_INCREMENT
      </code>
      value.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Create_time
      </code>
     </p>
     <p>
      When the table was created.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Update_time
      </code>
     </p>
     <p>
      When the data file was last updated. For some storage
            engines, this value is
      <code class="literal">
       NULL
      </code>
      . For example,
      <code class="literal">
       InnoDB
      </code>
      stores multiple tables in its
      <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
       system
            tablespace
      </a>
      and the data file timestamp does not
            apply. Even with
      <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
       file-per-table
      </a>
      mode with each
      <code class="literal">
       InnoDB
      </code>
      table in a separate
      <code class="literal">
       .ibd
      </code>
      file,
      <a class="link" href="glossary.html#glos_change_buffering" title="change buffering">
       change
            buffering
      </a>
      can delay the write to the data file, so
            the file modification time is different from the time of the
            last insert, update, or delete. For
      <code class="literal">
       MyISAM
      </code>
      , the data file timestamp is used;
            however, on Windows the timestamp is not updated by updates,
            so the value is inaccurate.
     </p>
     <p>
      <code class="literal">
       Update_time
      </code>
      displays a timestamp value
            for the last
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      ,
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      , or
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      performed on
      <code class="literal">
       InnoDB
      </code>
      tables that are not partitioned.
            For MVCC, the timestamp value reflects the
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        COMMIT
       </code>
      </a>
      time, which is
            considered the last update time. Timestamps are not
            persisted when the server is restarted or when the table is
            evicted from the
      <code class="literal">
       InnoDB
      </code>
      data dictionary
            cache.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Check_time
      </code>
     </p>
     <p>
      When the table was last checked. Not all storage engines
            update this time, in which case, the value is always
      <code class="literal">
       NULL
      </code>
      .
     </p>
     <p>
      For partitioned
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      tables,
      <code class="literal">
       Check_time
      </code>
      is always
      <code class="literal">
       NULL
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Collation
      </code>
     </p>
     <p>
      The table default collation. The output does not explicitly
            list the table default character set, but the collation name
            begins with the character set name.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Checksum
      </code>
     </p>
     <p>
      The live checksum value, if any.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Create_options
      </code>
     </p>
     <p>
      Extra options used with
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE
            TABLE
       </code>
      </a>
      .
     </p>
     <p>
      <code class="literal">
       Create_options
      </code>
      shows
      <code class="literal">
       partitioned
      </code>
      for a partitioned table.
     </p>
     <p>
      <code class="literal">
       Create_options
      </code>
      shows the
      <code class="literal">
       ENCRYPTION
      </code>
      clause for file-per-table
            tablespaces if the table is encrypted or if the specified
            encryption differs from the schema encryption. The
            encryption clause is not shown for tables created in general
            tablespaces. To identify encrypted file-per-table and
            general tablespaces, query 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>
      <code class="literal">
       ENCRYPTION
      </code>
      column.
     </p>
     <p>
      When creating a table with
      <a class="link" href="glossary.html#glos_strict_mode" title="strict mode">
       strict mode
      </a>
      disabled, the storage engine's default row format is
            used if the specified row format is not supported. The
            actual row format of the table is reported in the
      <code class="literal">
       Row_format
      </code>
      column.
      <code class="literal">
       Create_options
      </code>
      shows the row format that
            was specified in the
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE
            TABLE
       </code>
      </a>
      statement.
     </p>
     <p>
      When altering the storage engine of a table, table options
            that are not applicable to the new storage engine are
            retained in the table definition to enable reverting the
            table with its previously defined options to the original
            storage engine, if necessary.
      <code class="literal">
       Create_options
      </code>
      may show retained options.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Comment
      </code>
     </p>
     <p>
      The comment used when creating the table (or information as
            to why MySQL could not access the table information).
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="idm46045169157904">
   </a>
   Notes
  </h5>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For
      <code class="literal">
       InnoDB
      </code>
      tables,
      <a class="link" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
       <code class="literal">
        SHOW TABLE STATUS
       </code>
      </a>
      does not
            give accurate statistics except for the physical size
            reserved by the table. The row count is only a rough
            estimate used in SQL optimization.
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables, the output of
            this statement shows appropriate values for the
      <code class="literal">
       Avg_row_length
      </code>
      and
      <code class="literal">
       Data_length
      </code>
      columns, with the exception
            that
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      columns are not
            taken into account.
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables,
      <code class="literal">
       Data_length
      </code>
      includes data stored in main
            memory only; the
      <code class="literal">
       Max_data_length
      </code>
      and
      <code class="literal">
       Data_free
      </code>
      columns apply to Disk Data.
     </p>
    </li>
    <li class="listitem">
     <p>
      For NDB Cluster Disk Data tables,
      <code class="literal">
       Max_data_length
      </code>
      shows the space allocated
            for the disk part of a Disk Data table or fragment.
            (In-memory data resource usage is reported by the
      <code class="literal">
       Data_length
      </code>
      column.)
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <code class="literal">
       MEMORY
      </code>
      tables, the
      <code class="literal">
       Data_length
      </code>
      ,
      <code class="literal">
       Max_data_length
      </code>
      , and
      <code class="literal">
       Index_length
      </code>
      values approximate the
            actual amount of allocated memory. The allocation algorithm
            reserves memory in large amounts to reduce the number of
            allocation operations.
     </p>
    </li>
    <li class="listitem">
     <p>
      For views, most columns displayed by
      <a class="link" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
       <code class="literal">
        SHOW TABLE STATUS
       </code>
      </a>
      are 0 or
      <code class="literal">
       NULL
      </code>
      except that
      <code class="literal">
       Name
      </code>
      indicates the view name,
      <code class="literal">
       Create_time
      </code>
      indicates the creation time, and
      <code class="literal">
       Comment
      </code>
      says
      <code class="literal">
       VIEW
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Table information is also available from the
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   <a class="link" href="information-schema-tables-table.html" title="28.3.38 The INFORMATION_SCHEMA TABLES Table">
    <code class="literal">
     TABLES
    </code>
   </a>
   table. See
   <a class="xref" href="information-schema-tables-table.html" title="28.3.38 The INFORMATION_SCHEMA TABLES Table">
    Section 28.3.38, “The INFORMATION_SCHEMA TABLES Table”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
