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.