TABLE & COL COMMENTS 1

[jjpetrucelli]

Looking to create various documentation for the DB by using the comments for the table and the columns. A definition can be inserted, reports can be run and if any of the info needs to be changed, it can be changed in one spot but will be reflected in every report run! So i was trying to update col and / or tab comments (i.e. all_tab_comments, all_col_comments) but i cant. I get cannot modify a column which maps to a non key-preserved table. Now from my research it seems that i am attempting to update a view rather than the underlying table? How do / should I do this?

 

[SantaMufasa]

JJ,

You are correct: One should NEVER attempt to INSERT, UPDATE, DELETE, ALTER, or DROP a data dictionary object directly. The way we make modifications against a data dictionary object is with a Data Definition Language (DDL) command against a user-owned object.

Here are examples of correct methods by which we place comments on a table or a column:

SQL> comment on table s_emp is 'This is the employee table.';

Comment created.

SQL> comment on column s_emp.last_name is 'This is the surname for the employee.';

Comment created.

Does this provide answers you need?

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:28 (09Jun04) UTC (aka "GMT" and "Zulu"), 13:28 (09Jun04) Mountain Time)

 

[MPeccorini]

As always, Mufasa has the correct answer, however I would like to add that you can include both table and column comments in the table's creation command like this:

CREATE TABLE myTable (
col1 VARCHAR2(1) COMMENTS 'The comments for column',
col2 NUMBER(1) COMMENTS 'The comments for other column'
)
COMMENTS 'The comments for the table'
;

 

[SantaMufasa]

Amen, JJ. And from a documentation perspective, I prefer your method versus adding COMMENTS after the fact.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:14 (10Jun04) UTC (aka "GMT" and "Zulu"), 09:14 (10Jun04) Mountain Time)

 

[jaggiebunnet]

And if you want to go back to thru your tables you could try something like:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create table t (a varchar2(10), b number);

Table created.

SQL> select 'COMMENT ON TABLE ' || table_name || ' IS ''THIS IS THE ' || table_name || ' table'';' tab_com
  2  from user_tables  
  3  /

TAB_COM
--------------------------------------------------------------------------------
COMMENT ON TABLE T IS 'THIS IS THE T table';

SQL> select 'COMMENT ON TABLE ' || table_name || '.' || column_name || ' IS ''THIS IS THE ' || colum
n_name || ' column'';' col_com
  2  from user_tab_columns  
  3  /

COL_COM
--------------------------------------------------------------------------------
COMMENT ON TABLE T.A IS 'THIS IS THE A column';
COMMENT ON TABLE T.B IS 'THIS IS THE B column';

SQL>

 

[jjpetrucelli]

hey partner, im trying to figure out the best way to output this information on a webpage. Is there anyway i could set an alias on a particular col comment based on the column name? I want to ouput the col comments in a specific order and in some situations i may or may not want to ouput them, based on an if statement???