select
'DQMS' 库名,A.Table_Name 表名,C.comments 表中文名,A.column_name 字段名,B.comments 备注,A.data_type 数据类型,A.data_length 长度,A.data_precision 整数位,
case
when (select count(*) from user_cons_columns c
where c.table_name=A.TABLE_NAME and c.column_name=A.COLUMN_NAME and c.constraint_name=
(select d.constraint_name from user_constraints d where d.table_name=c.table_name and d.constraint_type ='P')
)>0 then '1' else '0'end as 主键,
null 外键,
A.nullable 允许空值,A.Data_default 缺省值
from
user_tab_columns A,user_col_comments B,user_tab_comments C
where
A.Table_Name = B.Table_Name
and A.Column_Name = B.Column_Name
and A.Table_Name = C.Table_Name
--and A.Table_Name = 'ACT_RU_TASK'
order by A.Table_Name
;
外键:
select distinct(ucc.column_name) column_name,ucc.table_name
--,rela.table_name,rela.column_name column_name1
from
user_constraints uc,user_cons_columns ucc,
(select t2.table_name,t2.column_name,t1.r_constraint_name from user_constraints t1,user_cons_columns t2 where t1.r_constraint_name=t2.constraint_name
--and t1.table_name='ONLINEXLS'
) rela
where
uc.constraint_name=ucc.constraint_name
and uc.r_constraint_name=rela.r_constraint_name
--and uc.table_name='PRODUCT'
order by ucc.table_name,ucc.column_name
;