简单的数据表统计

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-767212/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-767212/