Sir Robert Sir Robert - 2 months ago 8
MySQL Question

How can I access the table comment from a mysql table?

How can I get just the table comment from a mysql table? I tried the following, but they didn't work for various reasons. I want to figure out how to get just the string 'my comment' (ideally via perl =)

Any help?

-- Abbreviated output for convenience.
SHOW TABLE STATUS WHERE Name="foo"
+------+--------+---------+------------+------+----------------+---------------+
| Name | Engine | Version | Row_format | Rows | Create_options | Comment |
+------+--------+---------+------------+------+----------------+---------------+
| foo | MyISAM | 10 | Fixed | 0 | | my comment |
+------+--------+---------+------------+------+----------------+---------------+


and

SHOW CREATE TABLE foo;
+-------+------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------+
| fooo | CREATE TABLE `fooo` (`id` int(11) NOT NULL PRIMARY KEY) COMMENT='my comment' |
+-------+------------------------------------------------------------------------------+

Answer

Based on the answer by OMG Ponies, but using INFORMATION_SCHEMA.TABLES instead of INFORMATION_SCHEMA.COLUMNS. When looking around on the web, all I could find was info on the columns' comments, but never on the table's. This is how to get a table's comment.

SELECT table_comment 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE table_schema='my_cool_database' 
        AND table_name='user_skill';

+--------------------------+
| table_comment            |
+--------------------------+
| my awesome comment       | 
+--------------------------+