簡單的資料表統計

USER_CONS_OBJ_COLUMNS
USER_CONS_OBJ_COLUMNS displays information about the types that object columns (or attributes) or collection elements have been constrained to,
in the tables owned by the current user. Its columns (except for OWNER) are the same as those in ALL_CONS_OBJ_COLUMNS.

USER_CONS_COLUMNS
USER_CONS_COLUMNS describes columns that are owned by the current user and that are specified in constraint definitions.
Its columns are the same as those in "ALL_CONS_COLUMNS".

在當前使用者的約束中用到的列都會在檢視user_cons_columns中記錄。
也就是說，如果我要顯示哪些列是主鍵、外來鍵，可以通過此檢視來獲取。

檢視
select ucc.table_name,ucc.column_name,ucc.constraint_name,
decode(uc.constraint_type,'P','主鍵','F','外來鍵')
from user_cons_columns ucc
inner join user_constraints uc
on ucc.constraint_name=uc.constraint_name
where ucc.constraint_name not like '%$%'
and uc.constraint_type in ('P','F');

現在要得到一個表格，需要的列為：
表名稱 欄位名稱 中文解釋 型別 備註

備註中顯示該列是否為主鍵、外來鍵

我的想法是：
select a.table_name,a.column_name,b.comments ,a.data_type,
/*ucc.constraint_name,*/decode(uc.constraint_type,'P','主鍵','R','外來鍵')
from dba_tab_cols a
left join dba_col_comments b
on a.table_name=b.TABLE_NAME
and a.column_name=b.column_name
left join user_cons_columns ucc
on ucc.table_name=a.table_name
and ucc.column_name=a.column_name
and ucc.constraint_name not like '%$%'
left join user_constraints uc
on ucc.constraint_name=uc.constraint_name
and uc.constraint_type in ('P','R')
where a.owner = 'ATT'
and a.table_name not like '%$%'
and a.table_name not in ('TEST','T')
order by a.table_name,a.column_id;

但對於既是主鍵、也是外來鍵的列，不能用此方法，因為其會產生兩條記錄。
根源在user_cons_columns，該表中既是主鍵，也是外來鍵的列，對應兩條記錄。

最後我們將結果改成了以下形式
TABLE_NAME         COLUMN_NAME                    COMMENTS                       DATA_TYPE            PK_YN  FK_YN

pk_yn、fk_yn分別表示是否為主、外來鍵。

若生成html格式的結果，步驟為：

column table_name format a30
column column_name format a30
column comments format a50
column data_type format a20
column pk_yn format a5
column fk_yn format a5
set linesize 3000
set pagesize 10000
set trimspool on
set mark html on entmap off
spool a.html
spool off