• Leistungsspektrum
    Die passenden Lösungen für Ihre Anforderungen.
    Mehr Details unter der Rubrik: Leistungen
  • Professionelle Anwendungen
    Holen Sie mehr aus Ihren APEX Applikationen heraus.
  • Neueste Designs
    Nutzung von erweiterten Web 2.0 Möglichkeiten und Entwicklung von Corporate Designs
  • Individuelle Lösungen
    Entwicklung von Business-Anwendungen genau nach Ihren Wünschen.
  • Upgrade Lösungen
    Weiterentwicklung bestehender Anwendungen.
  • Schulungen und Vorträge
    Die passenden Schulungen genau für Sie zugeschnitten. Beispiel: Navigationsbeispiele
  • Plugins und Third Party Erweiterungen
    Erweitern Sie die Funktionalität ihrer Anwendungen mit einem modularen Entwicklungsansatz.

07 June, 2012

Select table and column comments (Oracle SQL)

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 comments: