[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

MYSQL Data Dictionary with COMMENT information?

Hi all

I need to associate and then extract more meaningful information for each of my columns in my table ...For Example: My column name 'ppc_1_2' stores information for the question "What is your name?" ..so I want to associate "What is your name?" with that column in MySQL

I figured out how to add comments to my fields ...

ALTER TABLE `myTable`.`government_form` MODIFY COLUMN `ppc_1_2` INT(7) NOT NULL AUTO_INCREMENT COMMENT 'What is your name?';

But how would I go about extracting that COMMENT information again in a SELECT statement, so I can essentially make a data dictionary for my table

Is this the best way for me to go about creating a data dictionary?

Cheers,
Mark
0
msiedle
Asked:
msiedle
  • 4
  • 2
1 Solution
 
todd_farmerCommented:
What version of MySQL do you have?  If 5.0 or above, the INFORMATION_SCHEMA database might be of use to you.
0
 
todd_farmerCommented:
SELECT table_schema, table_name, column_name, column_comment FROM information_schema.columns ORDER by 1, 2, 3;
0
 
todd_farmerCommented:
You can also try using the following syntax for pre-5.0 versions:

SHOW FULL COLUMNS FROM tableName;
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
msiedleAuthor Commented:
I'm running mysql 4.0.20 currently, so I ran your SHOW FULL COLUMNS FROM tableName successfully and it spat out a lot of info about my table but not my COMMENT information, so maybe my ALTER query didn't add the COMMENTS after all?

Do you know if I add COMMENTS pre-5.0 ?

If not, I might look into upgrading to mysql 5 and that INFORMATION_SCHEMA you mention above

Thanks
Mark
0
 
todd_farmerCommented:
According to the MySQL website, the above command only shows comments with 4.1 or greater:

The FULL keyword can be used from MySQL 3.23.32 on. It causes the output to include the privileges you have for each column. As of MySQL 4.1, FULL  also causes any per-column comments to be displayed.

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

MySQL 5.0 packs a lot of new feature (stored procedures, triggers, views, information_schema).  It's worth checking out if you have the flexibility.

0
 
msiedleAuthor Commented:
Great ...thanks for you help. I think it's probably easier for me to look at upgrading mysql as you say

Cheers,
Mark
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now