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

Listing tables and their structure with the MySQL Command Line Client

The MySQL Command Line client allows you to run sql queries from the a command line interface. This post looks at how to show the tables in a particular database and describe their structure. This is the continuation of a series about the MySQL Command Line client. Previous posts include Using the MySQL command line tool and Running queries from the MySQL Command Line.
After logging into the MySQL command line client and selecting a database, you can list all the tables in the selected database with the following command:

mysql> show tables;

(mysql> is the command prompt, and "show tables;" is the actual query in the above example).
In a test database I have set up, this returns the following:

+----------------+
| Tables_in_test |
+----------------+
| something      |
| something_else |
+----------------+
2 rows in set (0.00 sec)

This shows us there are two tables in the database called "something" and "something_else". We can show the structure of the table using the "desc" command like so for the "something" table:

mysql> desc something;

My test database table returns a result like so, showing there are 4 columns and what types etc they are:

+--------------+------------------+------+-----+-------------------+----------------+
| Field        | Type             | Null | Key | Default           | Extra          |
+--------------+------------------+------+-----+-------------------+----------------+
| something_id | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| name         | varchar(50)      | NO   |     | NULL              |                |
| value        | varchar(50)      | NO   |     | NULL              |                |
| ts_updated   | timestamp        | YES  | MUL | CURRENT_TIMESTAMP |                |
+--------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

Finally, you can show the indexes from a particular table like so:

mysql> show keys from something;

My test database has two indexes (these are labelled in the "key" column from the "desc something" output above as PRI and MUL). The output from the above command looks like this:

+-----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name   | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| something |          0 | PRIMARY    |            1 | something_id | A         |           2 |     NULL | NULL   |      | BTREE      | NULL    |
| something |          1 | ts_updated |            1 | ts_updated   | A         |        NULL |     NULL | NULL   |      | BTREE      | NULL    |
+-----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Summary

The MySQL Command Line client is useful for running queries as well as displaying what tables are in a MySQL database, the structure of those tables and the indexes in those tables as covered in this post.
Related posts:

• List stored procedures in MySQL
• Show indexes for a table with MySQL
• How to tell which storage engine a MySQL table uses
• Insert multiple records into MySQL with a single query