Hello All -

Sometimes I forget the exact purpose for a specific table field, so last night I decided I'll go ahead and comment all my table fields so I won't feel in a muddle.

So, as a test, I took the Create Table query from an existing table, changed the name, and added comments as shown here:

*******************************

CREATE TABLE `RoomTest' (
`RoomID` int(11) NOT NULL auto_increment COMMENT 'Record ID',
`RoomNumber` char(1) NULL COMMENT 'Individual Alpha Room Number',
`FloorNumber` tinyint(1) NULL COMMENT 'Floor room belongs on: 1 thru 3',
`RoomCategory_FK` varchar(25) NULL COMMENT 'Room category for each room: Standard, Deluxe, Family, Penthouse',
PRIMARY KEY (`RoomID`)
) TYPE=InnoDB

*******************************

The query ran fine without errors and the table was created. What I was surprised about, was that none of the comments were showing. Hummm. So I researched the mysql site and saw some stuff regarding "SHOW CREATE TABLE" and "SHOW FULL COLUMNS". So I tried running those queries, but still no comments.

So, why can't I view the field comments I put in? Is it because I created the comments in a DB that already exists? Do I need to somehow change the DB definition or something to show my comments?

Do I need to ut the SHOW CREATE TABLE or SHOW FULL COLUMNS after the CREATE TABLE block - like after the TYPE=InnoDB?

I'm very confused about something that I think should be so simple and of obvious documentary use.

I am using a mysql front end tool called SQLyog v3.71 (which is pretty old) but would that be the cause of my delimma?

At the end of the day, I'm sure it's something I'm doing wrong - could someone please shed some light on how I can make this work?

All assistance greatly appreciated,

Expatriate

i just took your CREATE TABLE statement, corrected the wee quote problem around the table name, ran it into my database, then did a SHOW CREATE TABLE, and vwalah, the comments are there

r937 -

Thanks for the heads up on the CREATE TABLE syntax. I must admit, I've NEVER created create table statements by hand before - I have just used sqlyog to do it for me which on the one hand is nice, but on the other hand I don't know what's really going on.

(An aside: should one, or shouldn't one, use any single quotes around table and field names??? I see it done both ways in different on-line examples)

Anyway... I amended the statement as shown below (no quotes around anything but the comments):

CREATE TABLE RoomTest2 (
RoomID int(11) NOT NULL auto_increment COMMENT 'Record ID',
RoomNumber char(1) NULL COMMENT 'Individual Alpha Room Number',
FloorNumber tinyint(1) NULL COMMENT 'Floor room belongs on: 1 thru 3',
RoomCategory_FK varchar(25) NULL COMMENT 'Room category for each room: Standard, Deluxe, Family, Penthouse',
PRIMARY KEY (RoomID)
) TYPE=InnoDB

Ran it, did a SHOW CREATE TABLE roomtest2 in sqlyog's query window, and still came up with no comments - see attached: "no_comments.jpg". Arg! Frustrating!!

I was also under the impression that any comments would show up in the view shown in the attached: "no_comments2.jpg" but still no dice.

Either it's me - probably - or maybe that I'm just using such an old version of sqlyog that it doesn't show comments, but I'd sure like to find the key to this to both remind me of what I've done and to be more professional in my db design tasks.

Is my statement SHOW CREATE TABLE correct?

When I tried SHOW FULL COLUMNS FROM roomtest2 I got the same result - attached no_comments3.jpg.

Any ideas where I'm going wrong here?

Expatriate

single quotes? never!! that would change the name from an identifier to a string

backticks? allowed, but not necessary unless the table name is a reserved word or contains special characters, both of which practices are heavily discouraged

and if sqlyog doesn't show you the column comments, then try another front end like HeidiSQL which does

Did you try viewing the table in the mysql client? If you can see the comments there then I would guess SQLyog would be the problem.

r937 and guelphdad -

Before I did my original post, I should have paid more attention to the first post in this forum about upgrading my server... Indeed, I was running a pre 4.1 version of mysql. I just upgraded - along with my mysql front end - and now I've got all the field-level comments I want.

Really do appreciate all of your attempts to help, and very sorry for wasting you time on this considering I didn't have the right version in the first place.

'll be more careful with my reading of important alerts in the future.

Expatriate