Get a MySQL table structure from the INFORMATION_SCHEMA

There are at least two ways to get a MySQL table's structure using SQL queries. The first is using DESCRIBE (which I have already covered in an earlier post) and the second by querying the INFORMATION_SCHEMA. This post deals with querying the INFORMATION_SCHEMA which has more information available than using DESCRIBE.

Example table

The example table used in this post was created with the following SQL, and is the same as in the earlier post:

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 the INFORMATION SCHEMA

The SQL query to get the table structure from the INFORMATION SCHEMA is as follows, where the database name is "test" and the table name is "products":

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME = '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:

+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+---------------------+------------+----------------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE         | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+---------------------+------------+----------------+---------------------------------+----------------+
| NULL          | test         | products   | product_id  |                1 | NULL           | NO          | int       |                     NULL |                   NULL |                10 |             0 | NULL               | NULL              | int(10) unsigned    | PRI        | auto_increment | select,insert,update,references |                |
| NULL          | test         | products   | url         |                2 | NULL           | NO          | varchar   |                      100 |                    100 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(100)        | UNI        |                | select,insert,update,references |                |
| NULL          | test         | products   | name        |                3 | NULL           | NO          | varchar   |                       50 |                     50 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(50)         |            |                | select,insert,update,references |                |
| NULL          | test         | products   | description |                4 | NULL           | NO          | varchar   |                      255 |                    255 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(255)        |            |                | select,insert,update,references |                |
| NULL          | test         | products   | price       |                5 | NULL           | NO          | decimal   |                     NULL |                   NULL |                10 |             2 | NULL               | NULL              | decimal(10,2)       |            |                | select,insert,update,references |                |
| NULL          | test         | products   | visible     |                6 | 1              | NO          | tinyint   |                     NULL |                   NULL |                 3 |             0 | NULL               | NULL              | tinyint(1) unsigned | MUL        |                | select,insert,update,references |                |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+---------------------+------------+----------------+---------------------------------+----------------+

As you can see there is a lot of information available.

Related posts:

• MySQL: SQL to drop a column from a table
• Get a MySQL table structure with DESCRIBE
• Find which tables use INNODB with MySQL
• Show indexes for a table with MySQL
• How to tell which storage engine a MySQL table uses
• Check if a MySQL table exists