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.
