CODEWITHSUNDEEP
sqloracle-database
Dr. Hans-Peter Störr
Dr. Hans-Peter Störr

Reputation: 25976

Oracle create table with column comments

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',
 ...
)

Upvotes: 16

Views: 40115

Answers (4)

luis.espinal
luis.espinal

Reputation: 10519

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.

Upvotes: -1

Dr. Hans-Peter Störr
Dr. Hans-Peter Störr

Reputation: 25976

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.

Upvotes: 4

APC
APC

Reputation: 146199

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.

Upvotes: 24

Phil
Phil

Reputation: 2422

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

Upvotes: 6

Related Questions