Apr 152010
 

So, you have a nice new database table, and you forgot (or couldn’t) add comments to the columns when you created it. I recently ran into this problem, and my usual internet searches didn’t turn up much.  One would expect a statement similar to this:
ALTER COLUMN `columnname` ADD COMMENT 'a comment'
However, that statement doesn’t exist.

If you have gotten to my post, you probably realize that there is no easy way to add comments (unless you have a nice application that does it for you). The real statement is something along the lines of this:
ALTER TABLE `courses` CHANGE `courseid` `courseid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'a new comment'

Which means you need to know the table definition, and restate it every time you want to change the comment. If you are doing this in bulk, that is a pain in the butt.

So, I wrote this little script that will take a create table definition, and turn it into Alter Table commands, that will add comments to each of your columns.

All you need to do is run ‘SHOW CREATE TABLE `tablename`’ on your database table, and insert that definition into my script here, and it will spit out a series of Alter Table calls with a space for a comment appended to the end.

The script is very basic at this point, and expects the formatting as shown in the example. The GUI I use, DbVisualizer, as well as the mysql command line client spit this out. So hopefully yours will to.

If you find this useful, or run into any problems with the script, please let me know.

Edit (April 15, 2010): Uploaded v0.2. Small mistake that was placing the column name in place of the table name. Sorry to anyone that was used it before and noticed it.
Edit (November 17, 2013): You can download the php script here for running on your own server.

  9 Responses to “Add Comments to Column – MySQL”

  1. Great !
    I’d just have let the eventual comments already present in the generated code.
    So it’d be possible to export comments from a table and apply them to the same table in a copy of the db.

  2. Have you filed a bug with mysql on this issue? I think it would be a great enhancement if they added the ability to add a comment to a column without locking the table or redefining the column type….

  3. As a followup, I opened a bug for this issue:

    http://bugs.mysql.com/bug.php?id=64439

  4. il y a t’il possiblité de copier les commentaires ( des plusieurs ) dans une autre table autre table pour s’en servir plus facilement srtt pour les couche de mapping comme nhibernate ?

  5. sorry for the stupid question.

    how can we see comments from command line?

  6. If from the mysql command line:

    show full columns from dbname.tablename;

    should do the trick.

  7. This script helps A LOT!!!

    If the script preserves what’s in the COMMENT option in the original SQL, it would be more wonderful.

    Cheers,

  8. Thanks for that script! Any chance of you showing the PHP code, so we can run it ourselves without submitting our schemas through your web server?

  9. I’ve updated the above post with a link to a .zip file which you can download and put on your own PHP server.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>