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
-

Facing issues? Have Questions? Post them here! I am happy to answer!


Author Info:

Rakesh (He/Him) is a seasoned developer with over 10 years of experience in web and app development, and a deep knowledge of operating systems. Author of insightful How-To articles for Code2care.

Follow him on: X

You can also reach out to him via e-mail: rakesh@code2care.org

Copyright © Code2care 2024 | Privacy Policy | About Us | Contact Us | Sitemap