Show Columns - (MySQL Table & Views)

The SHOW COLUMNS statement is used to display the columns and their information in a MySQL table or view. In this tutorial, we'll go over the syntax and usage of the SHOW COLUMNS statement and provide an example.

Syntax

SHOW COLUMNS FROM table_name;

Example

Consider the following employees table:

id	name	age	salary
1	John	28	60000
2	Jane	35	75000
3	Mark	42	90000

To show the columns and their information in this table, we would use the following SHOW COLUMNS statement:

SHOW COLUMNS FROM employees;

Output

The above query will produce the following output:

+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(50) | NO   |     | NULL    |                |
| age    | int(11)     | NO   |     | NULL    |                |
| salary | int(11)    | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

This output displays the columns in the employees table, along with their data type, nullability, key constraints, default values, and any extra information.

Explanation

The SHOW COLUMNS statement is used to get the columns and their information from a MySQL table or view. When executed, the statement will return a result set that contains a row for each column in the table or view. Each row contains information about a single column, including its name, data type, nullability, key constraints, default value, and any extra information.

Use

The SHOW COLUMNS statement can be useful when you need to retrieve information about a table's schema or when you need to debug database-related issues.

Important Points

• The SHOW COLUMNS statement only works with MySQL tables and views.
• The statement only returns metadata information about the table or view columns, not actual data.
• The output contains useful information that can be used to understand the structure of a table or view and its columns.

Summary

In this tutorial, we covered the syntax, example, output, explanation, use, and important points of the SHOW COLUMNS statement in MySQL. The statement is useful when you need to retrieve information about a table's columns and their metadata. The output of the statement contains information about each column's data type, nullability, key constraints, default value, and extra information.