How to view table schema in Mysql

Syntax : SHOW CREATE TABLE tablename

When To Use ?

• You already have a table in the database and you want to view how this table was created.
• You want to check all the datatypes, default value, nullability of the columns.
• You want to see all the constraints and keys that are applicable to this table.
• This command can be used to recreate the table as it is with all constraints and keys set. 

Example

Suppose we have two tables Institute and User  and we want to check the schema of the tables or in simple terms how these tables were created, then we will use this command SHOW CREATE TABLE user, where user is the table name whose schema we want to check.
The above command will give the following output.

user  | CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `city` varchar(255) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `dob` datetime DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `firstName` varchar(30) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `is_enabled` tinyint(1) NOT NULL,
  `is_expired` tinyint(1) DEFAULT NULL,
  `lastName` varchar(30) DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  `mobile` bigint(20) DEFAULT NULL,
  `pwd` varchar(255) NOT NULL,
  `token` varchar(255) DEFAULT NULL,
  `user_role` int(11) NOT NULL,
  `institute_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_52d8b7773f334f29885182c5fad` (`email`),
  KEY `FK_d87d9b2cd99f4f999b750d83637` (`institute_id`),
  CONSTRAINT `FK_d87d9b2cd99f4f999b750d83637` FOREIGN KEY (`institute_id`) REFERENCES `institute` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |