SELECT-ing Column Comments in MySQL

I started a project today that required I access a client's existing MySQL tables to add some data and utilize the database for a web app. The client's column names are codified into capitalized, 7 character strings that, as far as I can tell, have almost no discernible way to determine what data the column actually holds. As I was browsing around and getting familiar with the database table I realized that phpMyAdmin was showing full text names for these columns below the coded column name. Upon further investigation I was able to determine these full text names were stored in a field titled "Comments".

My goal was to figure out a way to query these column comments so that I could use the full text comments as display labels for the app I was writing (and also so I wouldn't go crazy trying to memorize the column name codes). The problem: I had no idea where those comments were stored or how to get at them. Google to the rescue!! (or so I thought.) After several fruitless searches I uncovered this blog post describing how to create a table with comments. Armed with the quesries from that post I started hacking SQL statements in phpMyAdmin starting with this one:

select columnname, columncomment from columns where table_name= 'your-table' ;

This query threw some errors so I started breaking it down. Eventually I ended up with this:

SELECT column_comment FROM information_schema.columns WHERE table_name='your-table';

This finally worked and I was able to retrieve the comments for the columns and continue on my way. I'm not sure how many times this sort of thing is done out there in the wild so I figured I'd pass on my experience. Hopefully no one has to waste their time figuring this one out in the future.

No comments:

Post a Comment


Bookmark and Share