Print table documentation with comments?

Question

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 species|

What are my options? Does MySQL workbench export such documentation?

Answer 1

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>