# 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) *This page is licensed: GPLv2, originally from* [*fill\_help\_tables.sql*](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql) {% @marketo/form formId="4316" %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-columns.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.