Print table documentation with comments?

I have a MySQL database, and I have used MySQL Workbench to insert text into the "comment" fields of many of the columns and tables; the plan was to have the documentation stored in the database.

However, I do not know how to either a) add a comment to a column or, more importantly b) retrieve this information.

I have looked through the limited MySQL documentation on Describe, Show Columns, and Show Extensions.

For example, I would like to return this:

mysql> describe dogs;

+----------------+-------------------------------+
| Field          | Comment                       | 
+----------------+-------------------------------+
| id             | primary key                   | 
| Name           | Dog's name                    | 
| species_id     | Foreign key to look up species|

Instead of

mysql> describe dogs;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment | 
| name           | varchar (255)| YES  | MUL | NULL    |                | 
| species_id     | int(11)      | YES  | MUL | NULL    |                |

Although this would do:

```
mysql> describe dogs;

mysql> describe dogs;
+----------------+--------------+------+-----+---------+----------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra | Comment |
+----------------+--------------+------+-----+---------+----------------+-------------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment | |
| name | varchar (255)| YES | MUL | NULL | | Dog's name |
| species_id | int(11) | YES | MUL | NULL | | Foreign key to look up sp

You can do this with normal DML/DDL.

Create a table with comments:

mysql> create table testcomments ( 
    -> col1 integer comment 'comment on integer column',
    -> col2 varchar(20) comment 'comment on varchat col');
Query OK, 0 rows affected (0.01 sec)

Select the information from the data dictionary (documentation link):

mysql> select column_name, column_comment
    -> from INFORMATION_SCHEMA.COLUMNS
    -> where TABLE_NAME = 'testcomments';
+-------------+---------------------------+
| column_name | column_comment            |
+-------------+---------------------------+
| col1        | comment on integer column |
| col2        | comment on varchat col    |
+-------------+---------------------------+
2 rows in set (0.00 sec)

mysql>

Add/modify a comment:

mysql> alter table testcomments
    -> change column col1
    -> col1 integer comment 'modified comment on integer column';

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select column_name, column_comment  from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'testcomments';
+-------------+------------------------------------+
| column_name | column_comment                     |
+-------------+------------------------------------+
| col1        | modified comment on integer column |
| col2        | comment on varchat col             |
+-------------+------------------------------------+
2 rows in set (0.00 sec)

mysql>

Modify a table comment:

mysql> alter table testcomments comment 'foo';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

View a table comment:

mysql> select TABLE_COMMENT
    -> from INFORMATION_SCHEMA.TABLES
    -> where TABLE_NAME = 'testcomments';
+---------------+
| TABLE_COMMENT |
+---------------+
| foo           |
+---------------+
1 row in set (0.00 sec)

mysql>

StackExchange Database Administrators Q#24554, answer score: 4