CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Sunil Chavan
Sunil Chavan

Reputation: 3004

SQL to add column and comment in table in single command

I am using Oracle 11g for my web application. I want to add a column and a comment to an existing table. I can do that easily with the below commands

ALTER TABLE product ADD product_description VARCHAR2(20)

and 

COMMENT ON COLUMN product.product_description 
     IS 'This is comment for the column';

But I want to do above task in single command. I searched on internet for a command to add a column and comment in a single command but I couldn't find. I wonder if this is possible. Any suggestions would be highly appreciated.

Upvotes: 66

Views: 265312

Answers (5)

Razia Rafi
Razia Rafi

Reputation: 1

Table Comments -RightClick on Table Name GoTo Properties Add to Extended Properties MS_Description as property name and add comments.

Upvotes: -1

Ravi Sambangi
Ravi Sambangi

Reputation: 361

You can use below query to update or create comment on already created table.

SYNTAX:

COMMENT ON COLUMN TableName.ColumnName IS 'comment text';

Example:

COMMENT ON COLUMN TAB_SAMBANGI.MY_COLUMN IS 'This is a comment on my column...';

Upvotes: 34

Suresh
Suresh

Reputation: 518

Query to add column with comment are :

alter table table_name 
add( "NISFLAG"    NUMBER(1,0) )

comment on column "ELIXIR"."PRD_INFO_1"."NISPRODGSTAPPL" is 'comment here'

commit;

Upvotes: 6

ratm
ratm

Reputation: 923

Add comments for two different columns of the EMPLOYEE table :

COMMENT ON EMPLOYEE
     (WORKDEPT IS 'see DEPARTMENT table for names',
     EDLEVEL IS 'highest grade level passed in school' )

Upvotes: -3

Ben
Ben

Reputation: 52863

No, you can't.

There's no reason why you would need to. This is a one-time operation and so takes only an additional second or two to actually type and execute.

If you're adding columns in your web application this is more indicative of a flaw in your data-model as you shouldn't need to be doing it.

In response to your comment that a comment is a column attribute; it may seem so but behind the scenes Oracle stores this as an attribute of an object.

SQL> desc sys.com$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                      NOT NULL NUMBER
 COL#                                               NUMBER
 COMMENT$                                           VARCHAR2(4000)

SQL>

The column is optional and sys.col$ does not contain comment information.

I assume, I have no knowledge, that this was done in order to only have one system of dealing with comments rather than multiple.

Upvotes: 40

Related Questions