Wednesday, November 12, 2008

Oracle: SQL to retrieve table and column comments

For the whole database
Select ut.owner,ut.table_name,ut.comments Table_Comments,uc.column_name,uc.comments Column_Comments
from dba_tab_comments ut, dba_col_comments uc,dba_tab_columns utc
where ut.owner = uc.owner
and uc.owner = utc.owner
and ut.table_name = uc.table_name
and ut.owner = 'Your_Owner_Name'
and uc.table_name = utc.table_name
and uc.column_name = utc.column_name
order by owner,ut.table_name

For the Schema
Select ut.table_name,ut.comments Table_Comments,uc.column_name,uc.comments Column_Comments,data_type
from USER_tab_comments ut, user_col_comments uc,user_tab_columns utc
where ut.table_name = uc.table_name
and uc.table_name = utc.table_name
and uc.column_name = utc.column_name
order by ut.table_name

No comments:

Post a Comment