MySQL SHOW COLUMNS Statement

MySQL SHOW COLUMNS Statement

The MySQL SHOW COLUMNS statement is used to display information about the columns in a specified table. This statement is essential for understanding the structure and schema details of a table.

Syntax

SHOW COLUMNS FROM table_name;

The SHOW COLUMNS statement has the following component:

  • table_name: The name of the table whose columns are to be shown.

Example MySQL SHOW COLUMNS Statement

Let's look at an example of the MySQL SHOW COLUMNS statement and how to use it:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    date_of_birth DATE
);

This query creates a table named employees with columns for id, first_name, last_name, email, and date_of_birth.

MySQL CREATE TABLE

Step 3: Showing the Columns

Display the columns of the table:

SHOW COLUMNS FROM employees;

This query displays information about each column in the employees table, including the column name, data type, nullability, key information, default value, and extra details.

MySQL SHOW COLUMNS

The output of the SHOW COLUMNS statement includes the following information:

  • Field: The name of the column.
  • Type: The data type of the column.
  • Null: Whether the column can contain NULL values.
  • Key: Whether the column is indexed (e.g., PRI for primary key).
  • Default: The default value of the column.
  • Extra: Any additional information (e.g., auto_increment).

This output provides detailed information about each column in the employees table.

Conclusion

The MySQL SHOW COLUMNS statement is a powerful tool for understanding the structure and schema of tables. Knowing how to use the SHOW COLUMNS statement is essential for effective database management and schema design in MySQL.