Mysql Show Table Comments

By Kevin Waterson 23954 12 Comments

CREATE TABLE product(
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'The primary key for this product',
        name varchar(50) NOT NULL  COMMENT 'The name of the product',
        description TEXT NOT NULL COMMENT 'Short note about this product'
) COMMENT='Table which holds data about products';

SHOW FULL COLUMNS FROM product;

The results will look like this

+-------------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+----------------------------------+
| Field       | Type        | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment                          |
+-------------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+----------------------------------+
| id          | int(11)     | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references | The primary key for this product |
| name        | varchar(50) | latin1_swedish_ci | NO   |     | NULL    |                | select,insert,update,references | The name of the product          |
| description | text        | latin1_swedish_ci | NO   |     | NULL    |                | select,insert,update,references | Short note about this product    |
+-------------+-------------+-------------------+------+-----+---------+----------------+---------------------------------+----------------------------------+

SELECT a.COLUMN_NAME, a.COLUMN_COMMENT
FROM information_schema.COLUMNS a
WHERE a.TABLE_NAME = 'product';

This time, the results are little more concise

+-------------------------------+--------------------------------------+
| COLUMN_NAME                   | COLUMN_COMMENT                       |
+-------------------------------+--------------------------------------+
| id                            | The primary key for this product     |
| name                          | The name of the product              |
| description                   | Short note about this product        | 
+-------------------------------+--------------------------------------+

To show the table comment, and not the comments from the individual fields, from the product table, use this query.

SELECT table_comment
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name='product';

The displayed result shows only the TABLE comment, and not the comments from the fields.

+---------------------------------------+
| table_comment                         |
+---------------------------------------+
|                                       |
| Table which holds data about products |
|                                       |
+---------------------------------------+