MySQL: 3 Ways of showing Table Definition and Structure

In MySQL, there are several possibilities to show the definitions and structures of tables. In this little tutorial, I would like to introduce and compare the individual methods.

As an example, I am showing each command at a fictional table "tab" consisting of two columns. In the following, we take a look at the statements "SHOW CREATE", "DESCRIBE" as well as "SHOW COLUMNS" for this table.

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" and execute the query.

SHOW CREATE tab

The output of SHOW CREATE is a SQL instruction containing all necessary commands to rebuild our table in the same way like it is existing. However, this instruction only contains the structure of the table with all column names, data types and index definitions, 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

We could now use exactly this result as a MySQL statement to create a table of the same design type.

DESCRIBE

If we are not interested in exporting the structure of a table because we only want to know of which columns a table is consisting without wanting to recreate the table, the clearer and better human readable DESCRIBE statement makes sense. A call of the "DESCRIBE" command for the table "tab" looks like this:

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. In addition to the name and the type of the columns, this tabletary representation, among other things, contains information about the default value of the columns, whether a column is part of an index (KEY) or which special conditions apply to a column (here, for example, the auto-increment of the id column).

Compared to the output of the command "SHOW CREATE", this type of representation is clearer and better readable for humans, but cannot be used directly to build a new twin table.

SHOW COLUMNS

"SHOW COLUMS" gives us the same result that we can also generate with the "SHOW CREATE" command. A call to "SHOW COLUMS" can look like this:

SHOW COLUMNS FROM tab

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