MySQL SHOW TABLES, DESCRIBE and SHOW CREATE TABLE Explained

Before you query a table, look at it. MySQL has four quick ways.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    category VARCHAR(30) NULL,
    KEY idx_category (category)
);
CREATE TABLE tags (id INT PRIMARY KEY, label VARCHAR(30));

1. SHOW TABLES: what is in this database?

query.sql

SQL

SHOW TABLES;

Output

OUTPUT

+-----------------+
| Tables_in_learn |
+-----------------+
| products        |
| tags            |
+-----------------+
2 rows in set

query.sql

SQL

SHOW TABLES LIKE 'pro%';

Output

OUTPUT

+------------------------+
| Tables_in_learn (pro%) |
+------------------------+
| products               |
+------------------------+
1 row in set

2. DESCRIBE: the columns

DESCRIBE products (or DESC products, or SHOW COLUMNS FROM products) lists each column:

query.sql

SQL

DESCRIBE products;

Output

OUTPUT

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(100) | NO   |     | NULL    |                |
| price    | decimal(8,2) | NO   |     | 0.00    |                |
| category | varchar(30)  | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set

Column	Meaning
`Field`	Column name
`Type`	Data type
`Null`	`YES` if `NULL` is allowed
`Key`	`PRI` primary key, `UNI` unique, `MUL` an index where values may repeat
`Default`	The default value
`Extra`	Extra behaviour such as `auto_increment`

3. SHOW CREATE TABLE: the exact definition

This prints the statement MySQL would use to rebuild the table, including indexes, character set and engine.

query.sql

SQL

SHOW CREATE TABLE products\G

Output

OUTPUT

*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `price` decimal(8,2) NOT NULL DEFAULT '0.00',
  `category` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set

It is the most accurate description, and the best thing to paste into a question or a code review.

4. INFORMATION_SCHEMA: ask with SELECT

query.sql

SQL

SELECT column_name, column_type, is_nullable, column_default, column_key
FROM information_schema.columns
WHERE table_schema = 'learn' AND table_name = 'products'
ORDER BY ordinal_position;

Output

OUTPUT

+-------------+--------------+-------------+----------------+------------+
| COLUMN_NAME | COLUMN_TYPE  | IS_NULLABLE | COLUMN_DEFAULT | COLUMN_KEY |
+-------------+--------------+-------------+----------------+------------+
| id          | int          | NO          | NULL           | PRI        |
| name        | varchar(100) | NO          | NULL           |            |
| price       | decimal(8,2) | NO          | 0.00           |            |
| category    | varchar(30)  | YES         | NULL           | MUL        |
+-------------+--------------+-------------+----------------+------------+
4 rows in set

Unlike SHOW, this is a normal SELECT: you can filter, sort and join it. It is also standard SQL, so the idea transfers to other databases.

Indexes

query.sql

SQL

SHOW INDEX FROM products;

Output

OUTPUT

+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| products |          0 | PRIMARY      |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | idx_category |            1 | category    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set

You will read this output properly in the Indexes chapter.

Which to use

Question	Use
What tables are here?	`SHOW TABLES`
What are the columns?	`DESCRIBE name`
What is the full definition?	`SHOW CREATE TABLE name`
I need this inside a script or a query	`INFORMATION_SCHEMA`

info

MySQL-specific

SHOW and DESCRIBE are MySQL commands. They are the fastest to type, so people use them interactively. Application code that must work on other databases uses INFORMATION_SCHEMA.