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

Question

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 1

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

Answer 2

If you don't want to have both database name and table name in the query, you can use :

SHOW TABLE STATUS WHERE Name='table_name'; 

and then pick up the "Comment" key of the result (you have to use an associative function like mysqli_fetch_assoc() in php).