How to obtain column comments from SQL

Question

As you guys propably know, there's the possibility to add a comment to a column in MySQL. Now I was wondering how I could obtain this comment via PHP/MySQL. I was searching the web but I didn't find any solution yet. Do you guys have any idea/solution for this problem?

Greetings!

@Chris, that link is for postgresql, and it does not mention `information_schema` how is that useful? — Johan, Jul 19 '11 at 18:36

score 13 · Accepted Answer · answered Jul 19 '11 at 18:30

13

SELECT
    COLUMN_COMMENT
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'db-name' AND
    TABLE_NAME = 'table-name' AND
    COLUMN_NAME = 'column-name'

http://dev.mysql.com/doc/refman/5.0/en/columns-table.html

answered Jul 19 '11 at 18:30

Hammerite

21,755
6
70
91

2

As a bonus, you can use `DATABASE()` to get the current default database name. – Wrikken Jul 19 '11 at 18:33
Very cool, didn't realize you could select the comment column as a pseudo field – AlienWebguy Jul 19 '11 at 18:53
you can use same data base with `SHOW FULL COLUMNS` in @AlienWebguy response – aya Nov 21 '15 at 14:04
How can I get a array containing all COLUMN_CONTENT 's ? – Nov 08 '17 at 05:22

score 8 · Answer 2 · answered Jul 19 '11 at 18:30

8

Just use this SQL:

SHOW FULL COLUMNS FROM myTable

http://dev.mysql.com/doc/refman/5.0/en/show-columns.html

The FULL keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.

answered Jul 19 '11 at 18:30

AlienWebguy

76,997
17
122
145

score 2 · Answer 3 · answered Jul 19 '11 at 18:30

Data regarding MySQL's tables in stored in the information_schema views.
You should be able to get it from there. This requires root privileges.

SELECT table_schema, table_name, column_comment 
FROM INFORMATION_SCHEMA.`columns` c
WHERE c.table_schema = 'mydatabase' 
  AND c.table_name = 'mytable'
  AND c.column_name = 'myfield'

score 2 · Answer 4 · answered Jul 19 '11 at 18:33

2

If you have correct privileges you could make this query:

$query = "SHOW FULL COLUMNS from node;";

$result = mysql_query($query);

And then fetch the results (there is a column named Comment that holds the comments)

answered Jul 19 '11 at 18:33

Nicola Peluchetti

76,206
31
145
192

score 1 · Answer 5 · answered Jul 19 '11 at 18:32

1

You can fetch those metadata from the information_schema database.

Ex:

SELECT column_name, column_comment FROM information_schema.columns WHERE table_name = 'user'

Where user is your table name.

answered Jul 19 '11 at 18:32

Boris Guéry

47,316
8
52
87

score 0 · Answer 6 · answered Jul 19 '11 at 18:31

0

Use the SQL command SHOW FULL COLUMNS as described in the MySQL manual. Its output contains the comments.

answered Jul 19 '11 at 18:31

Steffen

2,235
13
19

How to obtain column comments from SQL

6 Answers6

Linked