# SHOW COLUMNS

## Syntax

```sql
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](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/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](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/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:

```sql
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.                                  |
| VIRTUAL                      | The column was created with the VIRTUAL keyword.                                     |
| 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](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/describe) and [EXPLAIN](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/explain) can be used as shortcuts.

You can also list a table's columns with:

```
mariadb-show db_name tbl_name
```

See the [mariadb-show](https://mariadb.com/docs/server/clients-and-utilities/administrative-tools/mariadb-show) command for more details.

The [DESCRIBE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/describe) statement provides information similar to `SHOW COLUMNS`. The [information\_schema.COLUMNS](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-columns-table) table provides similar, but more complete, information.

The [SHOW CREATE TABLE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-table), [SHOW TABLE STATUS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-table-status), and [SHOW INDEX](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-index) statements also provide information about tables.

## Examples

```sql
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       |                |
+------------+----------+------+-----+---------+----------------+
```

```sql
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    |       |
+---------------+-------------+------+-----+---------+-------+
```

## See Also

* [DESCRIBE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/describe)
* [mariadb-show](https://mariadb.com/docs/server/clients-and-utilities/administrative-tools/mariadb-show)
* [SHOW CREATE TABLE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-table)
* [SHOW TABLE STATUS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-table-status)
* [SHOW INDEX](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-index)
* [Extended SHOW](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/extended-show)
* [Silent Column Changes](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/silent-column-changes)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}