How to show comments of a column with desc operation

Question

I want the desc table; operation to show the comments of the columns. I have seen that some people achieved this, however I could not find out how. Maybe it depends on the SQL Developer version, mine is 2.1.0.63. Database is Oracle 11g.

This is what I get when doing desc table;:

Desc table;
    Name                Nullable Type
    ------------------- -------- -----
    ID                  NOT NULL NUMBER(38)
    ITEM_ID                      NUMBER(38)

And I would like to get something like this:

Desc table;
    Name                Nullable Type        Comment
    ------------------- -------- ----------  ---------------------------------
    ID                  NOT NULL NUMBER(38)  Table's id
    ITEM_ID                      NUMBER(38)  Reference to an item

Answer 1

the desc command is interpreted differently for different tools. What it does is do a select of some standard Oracle views.

Here is a query on those views that will provide the desired column data, but I encourage you to do a select * to see all that is available.

You have 3 types of views, the dba_, all_, and user_* views. I use user_* because that is available for each schema/user, but it lists only the objects owned by that schema/user. The dba_ views are typically for dba's only, and the all_ views might or might not be available for you depending on how much your dba's trust you. ^_^

select tc.column_name
,      tc.nullable
,      tc.data_type || case when tc.data_type = 'NUMBER' and tc.data_precision is not null then '(' || tc.data_precision || ',' || tc.data_scale || ')'
                            when tc.data_type like '%CHAR%' then '(' || tc.data_length || ')'
                            else null
                       end type
,      cc.comments
from   user_col_comments cc
join   user_tab_columns  tc on  cc.column_name = tc.column_name
                            and cc.table_name  = tc.table_name
where  cc.table_name = upper(:tablename)