Get a MySQL table structure with DESCRIBE

There are at least two ways to get a MySQL table's structure using SQL queries. The first is using DESCRIBE and the second by querying the INFORMATION_SCHEMA. This post deals with the DESCRIBE function and the next MySQL postlooks at the INFORMATION_SCHEMA.

Example table

The example table used in this post was created with the following SQL:

CREATE TABLE `products` (
`product_id` int(10) unsigned NOT NULL auto_increment,
`url` varchar(100) NOT NULL,
`name` varchar(50) NOT NULL,
`description` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL,
`visible` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`product_id`),
UNIQUE KEY `url` (`url`),
KEY `visible` (`visible`)
)

Using DESCRIBE

The SQL query to get the table structure is:

DESCRIBE products;

You can run this from the MySQL CLI; phpMyAdmin; or using a programming language like PHP and then using the functions to retrieve each row from the query.

The resulting data from the MySQL CLI looks like this for the example table above:

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| product_id  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| url         | varchar(100)        | NO   | UNI | NULL    |                |
| name        | varchar(50)         | NO   |     | NULL    |                |
| description | varchar(255)        | NO   |     | NULL    |                |
| price       | decimal(10,2)       | NO   |     | NULL    |                |
| visible     | tinyint(1) unsigned | NO   | MUL | 1       |                |
+-------------+---------------------+------+-----+---------+----------------+

Using PHP

If you were using PHP, for example, you could do something like this:

$res = mysql_query('DESCRIBE products');
while($row = mysql_fetch_array($res)) {
    echo "{$row['Field']} - {$row['Type']}\n";
}

The output using the example table would be this:

product_id - int(10) unsigned
url - varchar(100)
name - varchar(50)
description - varchar(255)
price - decimal(10,2)
visible - tinyint(1) unsigned

Querying the INFORMATION_SCHEMA

My next MySQL post will look at how to do the same using the INFORMATION_SCHEMA, which provides more information than DESCRIBE, although I have found querying the INFORMATION_SCHEMA can run a little slowly sometimes myself.

Related posts:

• MySQL: SQL to drop a column from a table
• Get a MySQL table structure from the INFORMATION_SCHEMA