APEXblog.nl - Tips and Tricks

About This Blog:
I (Richard Weug) started this blog primary for myself to save all kinds of Apex tips and tricks in one place. To use it as a place to write down how I used some coding in my own projects, but also to copy and paste all kinds of articles I find on the Internet. (So I never have to wonder on what website did I read about??? When I see something interesting I collect the content so I have my own archive/knowlegde base.

View my profile on LinkedIn

 

Installation & Configuration

Print
11
Jun

Show table comments in SQL developer

Written by Richard Weug. Posted in Installation

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.

Original article: Tobias Arnhold