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 Show Table Structure

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)
Show Create Statement MySQL Query


Have Questions? Post them here!
Advertisements
Try Out Code2care Dev Tools:

Advertisements

Advertisements
Code2care is an initiative to publish and share varied knowledge in programming and technical areas gathered during day-to-day learnings and development activities.

Students and software developers can leverage this portal to find solutions to their various queries without re-inventing the wheel by referring to our easy to understand posts. Technical posts might include learnings, tutorials, trouble-shooting steps, video tutorials, code snippets, how-to, blogs, articles, etc.

🎉 We are celebrating the 10th years of Code2care! Thank you for all your support!

We strongly support Gender Equality & Diversity.