Select table and column comments (Oracle SQL)

Von Tobias Arnhold 6.07.2012
My default development client is the Oracle SQL Developer. If I compare it with TOAD it is slim, free to use and includes a table modeling area.
One thing I don't like is the ability to see table comments. To find this information you need to click on the table inside the table view ("Connections" > #MY_CON2# > Tables), then on tab: "Details", scroll to column "Comments" and finally double click on the comment field to see all details.

Instead of going this way each time you can use this select instead:
-- All tables inside my user
select table_name, comments 
from user_tab_comments
where table_name = :MY_TABLE;

-- All tables for all users:
select table_name, comments 
from all_tab_comments 
where owner = :MY_USER
and   table_name = :MY_TABLE;
To select the column comments use this select: (idea comes from bdelmee)
select TABLE_NAME,
  K.COLUMN_ID, COLUMN_NAME,
  K.NULLABLE, K.DATA_TYPE || 
    case when K.DATA_SCALE is not null then '(' || K.DATA_PRECISION || ',' || K.DATA_SCALE || ')'
      when K.DATA_PRECISION is not null then '(' || K.DATA_PRECISION || ')'
      when K.DATA_LENGTH is not null and K.DATA_TYPE like '%CHAR%' then '(' || K.DATA_LENGTH || ')'
    end DATA_TYPE,
  C.COMMENTS
from user_col_comments C join user_tab_cols K
using(TABLE_NAME,COLUMN_NAME)
where table_name = :MY_TABLE
order by TABLE_NAME, K.COLUMN_ID;
Now we join both selects together and get all information we need:
select 
  decode(column_id,0,TABLE_NAME,null) as TABLE_NAME,
  decode(column_id,0,null,column_id)  as COLUMN_ID,
  COLUMN_NAME, NULLABLE, DATA_TYPE, COMMENTS
from (
  select cc.TABLE_NAME,
    tc.COLUMN_ID, tc.COLUMN_NAME,
    tc.NULLABLE, tc.DATA_TYPE || 
      case when tc.DATA_SCALE is not null then '(' || tc.DATA_PRECISION || ',' || tc.DATA_SCALE || ')'
        when tc.DATA_PRECISION is not null then '(' || tc.DATA_PRECISION || ')'
        when tc.DATA_LENGTH is not null and tc.DATA_TYPE like '%CHAR%' then '(' || tc.DATA_LENGTH || ')'
      end DATA_TYPE,
    cc.COMMENTS
  from user_col_comments cc
  INNER JOIN user_tab_cols tc ON (cc.TABLE_NAME = tc.TABLE_NAME and cc.TABLE_NAME = tc.TABLE_NAME and cc.COLUMN_NAME = tc.COLUMN_NAME) 
  UNION
  select tab.table_name as TABLE_NAME, 
         0 as COLUMN_ID, '' as COLUMN_NAME,
         '' as NULLABLE, '' as DATA_TYPE,
         tab.comments as COMMENTS
  from user_tab_comments tab
) 
where table_name = UPPER(:MY_TABLE)
order by table_name, column_id  ;
That's it.

No Comment to " Select table and column comments (Oracle SQL) "