Home | About Chris Hope | Contact Chris Hope | Search:

Get a MySQL table structure with DESCRIBE

Posted July 22nd, 2009 in MySql

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 post looks 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.

Comments

blog comments powered by Disqus