Syntax
------ 
SHOW [FULL] {COLUMNS | FIELDS} FROM tbl_name [FROM db_name]
 [LIKE 'pattern' | WHERE expr]
 
Description
----------- 
SHOW COLUMNS displays information about the columns in a
given table. It also works for views. The LIKE clause, if
present on its own, indicates which column names to match.
The WHERE and LIKE clauses can be given to select rows using
more general conditions, as discussed in Extended SHOW.
 
If the data types differ from what you expect them to be
based on a
CREATE TABLE statement, note that MariaDB sometimes changes
data types when you create or alter a table. The conditions
under which this
occurs are described in the Silent Column Changes article.
 
The FULL keyword causes the output to include the column
collation and comments, as well as the privileges you have
for each column.
 
You can use db_name.tbl_name as an alternative to the
tbl_name FROM db_name syntax. In other words, these two
statements are equivalent:
 
SHOW COLUMNS FROM mytable FROM mydb;
SHOW COLUMNS FROM mydb.mytable;
 
SHOW COLUMNS displays the following values for each table
column:
 
Field indicates the column name.
 
Type indicates the column data type.
 
Collation indicates the collation for non-binary string
columns, or
NULL for other columns. This value is displayed only if you
use the
FULL keyword.
 
The Null field contains YES if NULL values can be stored in
the column,
NO if not.
 
The Key field indicates whether the column is indexed:
If Key is empty, the column either is not indexed or is
indexed only as a
 secondary column in a multiple-column, non-unique index.
If Key is PRI, the column is a PRIMARY KEY or
 is one of the columns in a multiple-column PRIMARY KEY.
If Key is UNI, the column is the first column of a
unique-valued
 index that cannot contain NULL values.
If Key is MUL, multiple occurrences of a given value are
allowed
 within the column. The column is the first column of a
non-unique index or a
 unique-valued index that can contain NULL values.
 
If more than one of the Key values applies to a given column
of a
table, Key displays the one with the highest priority, in
the order
PRI, UNI, MUL.
 
A UNIQUE index may be displayed as PRI if
it cannot contain NULL values and there is no
PRIMARY KEY in the table. A UNIQUE index
may display as MUL if several columns form a composite
UNIQUE index; although the combination of the columns is
unique, each column can still hold multiple occurrences of a
given value.
 
The Default field indicates the default value that is
assigned to the
column.
 
The Extra field contains any additional information that is
available about a given column.
 
Value | Description | 
 
AUTO_INCREMENT | The column was created with the
AUTO_INCREMENT keyword. | 
 
PERSISTENT | The column was created with the PERSISTENT
keyword. (New in 5.3) | 
 
VIRTUAL | The column was created with the VIRTUAL keyword.
(New in 5.3) | 
 
on update CURRENT_TIMESTAMP | The column is a TIMESTAMP
column that is automatically updated on INSERT and UPDATE. |

 
Privileges indicates the privileges you have for the column.
This
value is displayed only if you use the FULL keyword.
 
Comment indicates any comment the column has. This value is
displayed
only if you use the FULL keyword.
 
SHOW FIELDS is a synonym for
SHOW COLUMNS. Also DESCRIBE and EXPLAIN can be used as
shortcuts.
 
You can also list a table's columns with: 
 
mysqlshow db_name tbl_name
 
See the mysqlshow command for more details.
 
The DESCRIBE statement provides information similar to SHOW
COLUMNS. The information_schema.COLUMNS table provides
similar, but more complete, information.
 
The SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX
statements also provide information about tables.
 
Examples
-------- 
SHOW COLUMNS FROM city;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| Country | char(3) | NO | UNI | | |
| District | char(20) | YES | MUL | | |
| Population | int(11) | NO | | 0 | |
+------------+----------+------+-----+---------+----------------+
 
SHOW COLUMNS FROM employees WHERE Type LIKE 'Varchar%';
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| first_name | varchar(30) | NO | MUL | NULL | |
| last_name | varchar(40) | NO | | NULL | |
| position | varchar(25) | NO | | NULL | |
| home_address | varchar(50) | NO | | NULL | |
| home_phone | varchar(12) | NO | | NULL | |
| employee_code | varchar(25) | NO | UNI | NULL | |
+---------------+-------------+------+-----+---------+-------+
 


URL: https://mariadb.com/kb/en/show-columns/