MySQL Query for Schema/Structure of a Table

MySQL Get Structure of a Table

In order to get the schema or the structure of a MySQL table you can make use of the describe query,

Syntax:

description [db_schema_name].table_name;

or,

desc [db_schema_name].table_name;

Example:

mysql> describe student;

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int          | NO   | PRI | NULL    |       |
| student_name | varchar(255) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> desc uat_db.app_user;

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| user_id     | int          | NO   | PRI | NULL    |       |
| c_date_time | datetime(6)  | YES  |     | NULL    |       |
| u_date_time | datetime(6)  | YES  |     | NULL    |       |
| user_name   | varchar(255) | YES  | UNI | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

MySQL Get Create Statement of a table

To get the Create SQL statement for a table, make use of the show create table table_name query,

Syntax:

show create table [db_name.]table_name;

Example:

mysql> show create table app_user;

CREATE TABLE `app_user` (
  `user_id` int NOT NULL,
  `c_date_time` datetime(6) DEFAULT NULL,
  `u_date_time` datetime(6) DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `UK_cpt2jpnop7mcpds1sv2i5629w` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.01 sec)