<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="show-columns">
      </a>
      15.7.7.6 SHOW COLUMNS Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045171072624">
  </a>
  <a class="indexterm" name="idm46045171069856">
  </a>
  <a class="indexterm" name="idm46045171068784">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5250549"><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 punctuation">[</span><span class="token keyword">EXTENDED</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">FULL</span><span class="token punctuation">]</span> {<span class="token keyword">COLUMNS</span> <span class="token operator">|</span> <span class="token keyword">FIELDS</span>}
    {<span class="token keyword">FROM</span> <span class="token operator">|</span> <span class="token keyword">IN</span>} <em class="replaceable">tbl_name</em>
    <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-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
    <code class="literal">
     SHOW COLUMNS
    </code>
   </a>
   displays information
        about the columns in a given table. It also works for views.
   <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
    <code class="literal">
     SHOW COLUMNS
    </code>
   </a>
   displays information
        only for those columns for which you have some privilege.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34233700"><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">COLUMNS</span> <span class="token keyword">FROM</span> City<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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> Field       <span class="token punctuation">|</span> Type     <span class="token punctuation">|</span> Null <span class="token punctuation">|</span> Key <span class="token punctuation">|</span> Default <span class="token punctuation">|</span> Extra          <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> ID          <span class="token punctuation">|</span> int(11)  <span class="token punctuation">|</span> NO   <span class="token punctuation">|</span> PRI <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> auto_increment <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Name        <span class="token punctuation">|</span> char(35) <span class="token punctuation">|</span> NO   <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> CountryCode <span class="token punctuation">|</span> char(3)  <span class="token punctuation">|</span> NO   <span class="token punctuation">|</span> MUL <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> District    <span class="token punctuation">|</span> char(20) <span class="token punctuation">|</span> NO   <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> Population  <span class="token punctuation">|</span> int(11)  <span class="token punctuation">|</span> NO   <span class="token punctuation">|</span>     <span class="token punctuation">|</span> 0       <span class="token punctuation">|</span>                <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
   An alternative to
   <code class="literal">
    <em class="replaceable">
     <code>
      tbl_name
     </code>
    </em>
    FROM
    <em class="replaceable">
     <code>
      db_name
     </code>
    </em>
   </code>
   syntax is
   <em class="replaceable">
    <code>
     db_name.tbl_name
    </code>
   </em>
   . These two
        statements are equivalent:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75209196"><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">COLUMNS</span> <span class="token keyword">FROM</span> mytable <span class="token keyword">FROM</span> mydb<span class="token punctuation">;</span>
<span class="token keyword">SHOW</span> <span class="token keyword">COLUMNS</span> <span class="token keyword">FROM</span> mydb<span class="token punctuation">.</span>mytable<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The optional
   <code class="literal">
    EXTENDED
   </code>
   keyword causes the
        output to include information about hidden columns that MySQL
        uses internally and are not accessible by users.
  </p>
  <p>
   The optional
   <code class="literal">
    FULL
   </code>
   keyword causes the output
        to include the column collation and comments, as well as the
        privileges you have for each column.
  </p>
  <p>
   The
   <a class="link" href="string-comparison-functions.html#operator_like">
    <code class="literal">
     LIKE
    </code>
   </a>
   clause, if present,
        indicates which column 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>
   The data types may differ from what you expect them to be based
        on a
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement
        because MySQL sometimes changes data types when you create or
        alter a table. The conditions under which this occurs are
        described in
   <a class="xref" href="silent-column-changes.html" title="15.1.20.7 Silent Column Specification Changes">
    Section 15.1.20.7, “Silent Column Specification Changes”
   </a>
   .
  </p>
  <p>
   <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
    <code class="literal">
     SHOW COLUMNS
    </code>
   </a>
   displays the
        following values for each table column:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       Field
      </code>
     </p>
     <p>
      The name of the column.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Type
      </code>
     </p>
     <p>
      The column data type.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Collation
      </code>
     </p>
     <p>
      The collation for nonbinary string columns, or
      <code class="literal">
       NULL
      </code>
      for other columns. This value is
            displayed only if you use the
      <code class="literal">
       FULL
      </code>
      keyword.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Null
      </code>
     </p>
     <p>
      The column nullability. The value is
      <code class="literal">
       YES
      </code>
      if
      <code class="literal">
       NULL
      </code>
      values can be stored in the
            column,
      <code class="literal">
       NO
      </code>
      if not.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Key
      </code>
     </p>
     <p>
      Whether the column is indexed:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         If
         <code class="literal">
          Key
         </code>
         is empty, the column either is
                not indexed or is indexed only as a secondary column in
                a multiple-column, nonunique index.
        </p>
       </li>
       <li class="listitem">
        <p>
         If
         <code class="literal">
          Key
         </code>
         is
         <code class="literal">
          PRI
         </code>
         , the
                column is a
         <code class="literal">
          PRIMARY KEY
         </code>
         or is one of
                the columns in a multiple-column
         <code class="literal">
          PRIMARY
                KEY
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         If
         <code class="literal">
          Key
         </code>
         is
         <code class="literal">
          UNI
         </code>
         , the
                column is the first column of a
         <code class="literal">
          UNIQUE
         </code>
         index. (A
         <code class="literal">
          UNIQUE
         </code>
         index permits multiple
         <code class="literal">
          NULL
         </code>
         values, but you can tell whether
                the column permits
         <code class="literal">
          NULL
         </code>
         by checking
                the
         <code class="literal">
          Null
         </code>
         field.)
        </p>
       </li>
       <li class="listitem">
        <p>
         If
         <code class="literal">
          Key
         </code>
         is
         <code class="literal">
          MUL
         </code>
         , the
                column is the first column of a nonunique index in which
                multiple occurrences of a given value are permitted
                within the column.
        </p>
       </li>
      </ul>
     </div>
     <p>
      If more than one of the
      <code class="literal">
       Key
      </code>
      values
            applies to a given column of a table,
      <code class="literal">
       Key
      </code>
      displays the one with the highest priority, in the order
      <code class="literal">
       PRI
      </code>
      ,
      <code class="literal">
       UNI
      </code>
      ,
      <code class="literal">
       MUL
      </code>
      .
     </p>
     <p>
      A
      <code class="literal">
       UNIQUE
      </code>
      index may be displayed as
      <code class="literal">
       PRI
      </code>
      if it cannot contain
      <code class="literal">
       NULL
      </code>
      values and there is no
      <code class="literal">
       PRIMARY KEY
      </code>
      in the table. A
      <code class="literal">
       UNIQUE
      </code>
      index may display as
      <code class="literal">
       MUL
      </code>
      if several columns form a composite
      <code class="literal">
       UNIQUE
      </code>
      index; although the combination of
            the columns is unique, each column can still hold multiple
            occurrences of a given value.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Default
      </code>
     </p>
     <p>
      The default value for the column. This is
      <code class="literal">
       NULL
      </code>
      if the column has an explicit
            default of
      <code class="literal">
       NULL
      </code>
      , or if the column
            definition includes no
      <code class="literal">
       DEFAULT
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Extra
      </code>
     </p>
     <p>
      Any additional information that is available about a given
            column. The value is nonempty in these cases:
     </p>
     <a class="indexterm" name="idm46045171000016">
     </a>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          auto_increment
         </code>
         for columns that have
                the
         <code class="literal">
          AUTO_INCREMENT
         </code>
         attribute.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          on update CURRENT_TIMESTAMP
         </code>
         for
         <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
          <code class="literal">
           TIMESTAMP
          </code>
         </a>
         or
         <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
          <code class="literal">
           DATETIME
          </code>
         </a>
         columns that
                have the
         <code class="literal">
          ON UPDATE CURRENT_TIMESTAMP
         </code>
         attribute.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          VIRTUAL GENERATED
         </code>
         or
         <code class="literal">
          STORED
                GENERATED
         </code>
         for generated columns.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          DEFAULT_GENERATED
         </code>
         for columns that
                have an expression default value.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Privileges
      </code>
     </p>
     <p>
      The privileges you have for the column. This value is
            displayed only if you use the
      <code class="literal">
       FULL
      </code>
      keyword.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       Comment
      </code>
     </p>
     <p>
      Any comment included in the column definition. This value is
            displayed only if you use the
      <code class="literal">
       FULL
      </code>
      keyword.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Table column information is also available from 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. See
   <a class="xref" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
    Section 28.3.8, “The INFORMATION_SCHEMA COLUMNS Table”
   </a>
   . The extended
        information about hidden columns is available only using
   <code class="literal">
    SHOW EXTENDED COLUMNS
   </code>
   ; it cannot be obtained
        from the
   <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.
  </p>
  <p>
   You can list a table's columns with the
   <a class="link" href="mysqlshow.html" title="6.5.6 mysqlshow — Display Database, Table, and Column Information">
    <span class="command">
     <strong>
      mysqlshow
      <em class="replaceable">
       <code>
        db_name
       </code>
      </em>
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
     </strong>
    </span>
   </a>
   command.
  </p>
  <p>
   The
   <a class="link" href="describe.html" title="15.8.1 DESCRIBE Statement">
    <code class="literal">
     DESCRIBE
    </code>
   </a>
   statement provides
        information similar to
   <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
    <code class="literal">
     SHOW
        COLUMNS
    </code>
   </a>
   . See
   <a class="xref" href="describe.html" title="15.8.1 DESCRIBE Statement">
    Section 15.8.1, “DESCRIBE Statement”
   </a>
   .
  </p>
  <p>
   The
   <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
    <code class="literal">
     SHOW CREATE TABLE
    </code>
   </a>
   ,
   <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>
   , and
   <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
    <code class="literal">
     SHOW INDEX
    </code>
   </a>
   statements also
        provide information about tables. See
   <a class="xref" href="show.html" title="15.7.7 SHOW Statements">
    Section 15.7.7, “SHOW Statements”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    SHOW COLUMNS
   </code>
   includes the table's
        generated invisible primary key, if it has one, by default. You
        can cause this information to be suppressed in the
        statement's output by setting
   <a class="link" href="server-system-variables.html#sysvar_show_gipk_in_create_table_and_information_schema">
    <code class="literal">
     show_gipk_in_create_table_and_information_schema
        = OFF
    </code>
   </a>
   . For more information, see
   <a class="xref" href="create-table-gipks.html" title="15.1.20.11 Generated Invisible Primary Keys">
    Section 15.1.20.11, “Generated Invisible Primary Keys”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
