show create table tablename - useful SQL query

Hello,

This is a very useful query in I used in MySQL to find out the "create statement" that was used to create a table.

It helped me to copy and paste the table.

Syntax and sample output,

mysql> show create table User\G;

*************************** 1. row ***************************
       Table: User
Create Table: CREATE TABLE User (
  ID INT(11) default NULL auto_increment,
  NAME char(60) default NOT NULL,
  EMAIL char(100) default NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM


Unlike the query "desc tablename", which will give you the table definition,

we can get the table definition and the create statement from the

"show create table  tablename" query.


Guys, post your cases where you find this query useful.


-Krishna.