1 1 Vote

MySQL: 3 Ways of showing Table Definition and Structure

Info by NetGuy | 29/03/2014 at 19:04

In MySQL, there are several possibilities to show the definitions and structures of tables. In this Info, I would like to introduce the individual methods. As an example, I am showing each command at a fictional table "tab" consisting of two columns.

SHOW CREATE

If we want to copy our table onto another server, the SHOW CREATE statement is the best choice. We can simply write the name of our table after "SHOW CREATE".

SHOW CREATE tab

The output of SHOW CREATE is a SQL instruction containing all necessary commands to rebuild our table. However, this instruction only contains the structure of the table but no data.

Thus, a possible output of "SHOW CREATE tab" can look like this, when "tab" contains the columns "id" and "vname":

CREATE TABLE tab (
  id int(11) NOT NULL AUTO_INCREMENT,
  vname varchar(10) NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8

DESCRIBE

If we are not interested in exporting the structure of the table because we only want to know of which columns a table is consisting, the clearer DESCRIBE statement makes sense.

DESCRIBE tab

As a result, DESCRIBE is providing a table with a line for each field of our table. For the example above, the result would look like this:

FIELD   TYPE         NULL   KEY   DEFAULT   EXTRA
------------------------------------------------------------
id      int(11)      NO     PRI   NULL      auto_increment
vname   varchar(10)  NO           NULL

The output has 6 columns containing all information about the fields of the table.

SHOW COLUMNS

"SHOW COLUMS" gives us the same result.

SHOW COLUMNS FROM tab

In contrast to the other commands, when using "SHOW COLUMNS", we should not forget "FROM".

ReplyPositiveNegative

About the Author

Avatar AuthorThe author has not added a short description to his profile yet.
Show Profile | Message

 

Related Topics

HTML: Table - Merge Cells
Question | 1 Answer

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own articles on askingbox.com. How to do.