Oracle create table with column comments

Question

Is there a column comment syntax that allows me to specify a column comment directly where I declare the column in the create table statement (i.e. inline)? The 11g spec does not mention anything, on another page something is mentioned but I could not get it to work. There is a way to specify comments after creating the table, but I think it is annoying that the comment is separated from the field definition. I am looking for something like this (which does not work):

create table whatever (
 field number(15,0) primary key comment 'primary key generated from sequence pkseq',
 ...
)

APC · Accepted Answer · 2012-05-09T13:13:36.577

23

I'm afraid the "annoying" COMMENT ON syntax is the only way of doing this. SQL Server, PostgreSQL and DB2 use the same syntax (even though, as far as I know, there is no ANSI standard syntax for adding comments to database objects).

MySQL supports the way you would like it to work. I agree it would be a nicer mechanism, but in my experience so few people use comments at all that I doubt Oracle will ever change it.

edited May 09 '12 at 13:13

answered May 09 '12 at 12:58

APC

144,005
19
170
281

The `COMMENT ON` syntax is defined by the SQL standard if I'm not mistaken. – May 09 '12 at 12:59
2

@a_horse_with_no_name : My understanding is. that COMMENT ON is *not* part of the ANSI SQL standard, although other databases do use it – APC May 09 '12 at 13:11

score 6 · Answer 2 · answered May 09 '12 at 12:55

6

I'm afraid it can only be done after table creation, using the comment on column ... is '' syntax.

answered May 09 '12 at 12:55

Phil

2,392
18
21

Dr. Hans-Peter Störr · Answer 3 · 2012-11-22T13:25:56.460

4

A workaround to this annoying syntax is also to view and edit the tables in Oracles SQLExplorer. It contains a wizard that allows you to edit the comments right next to the columns. It even allows easy creation of alter table scripts.

My procedure when editing tables is to enter the changes in the wizard without actually executing them, then go to its DDL tab and retrieve the SQL from there (as update, not full create script) and press cancel on the wizard. Then I put the created SQL into the SQL script I am writing. Only when I am finished with the script I execute everything; I do never make any changes with the wizard itself.

edited Nov 22 '12 at 13:25

answered Jun 29 '12 at 08:46

Dr. Hans-Peter Störr

25,298
30
102
139

(Strictly seen, this is not an answer to my exact question, but it solved my pain that lead to that question quite nicely.) – Dr. Hans-Peter Störr Jun 29 '12 at 08:47
Does the wizard create a migration script that can be saved in project revision control? – Roboprog Nov 01 '12 at 23:44

score -1 · Answer 4 · answered Feb 08 '18 at 19:25

Test on sqlplus (or similar), but the syntax is as follows:

-- assuming you have privileges
COMMENT ON COLUMN SCHEMA1.TABLE1.COL1 
  IS 'My comment'

-- then you can double check like this
SELECT * FROM all_col_comments WHERE 
  (OWNER, TABLE_NAME, COLUMN_NAME) 
  IN (('SCHEMA1','TABLE1','COL1'));

Note that the comment will now show in SQLDeveloper (or Toad or whatever env you have) until you reopen said table's properties.

Similar syntax can be used to annotate tables, indexes and materialized views. [source: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4009.htm]

I understand similar syntax exists for MySQL and others, but it is not proper ANSI. It's very useful, though.

That got nothing to do with what was asked. The question is about NOT have to use that syntax but instead directly specifying the comment together with the column in the "CREATE TABLE" statement like can be done for constraints and index. — ABaumstumpf, Jun 18 '21 at 10:29

Oracle create table with column comments

4 Answers4

Linked