create view v_table_des
as
select a.owner æå±ç¨æ·,a.table_name 表å,a.column_name å段å,a.data_type å段类å,a.å段é¿åº¦,a.å段精度,a.æ¯å¦ä¸ºç©º,a.å建æ¥æ,a.æåä¿®æ¹æ¥æ,
case when a.owner=d.owner and a.table_name=d.table_name and a.column_name=d.column_name then '主é®' else '' end æ¯å¦ä¸»é®
from
(select a.owner,a.table_name,b.column_name,b.data_type,case when b.data_precision is null then b.data_length else data_precision end å段é¿åº¦,
data_scale å段精度,
decode(nullable,'Y','â','N','Ã') æ¯å¦ä¸ºç©º,c.created å建æ¥æ,c.last_ddl_time æåä¿®æ¹æ¥æ
from all_tables a,all_tab_columns b,all_objects c
where a.table_name=b.table_name and a.owner=b.owner
and a.owner=c.owner
and a.table_name=c.object_name
and a.owner='SCOTT'--æ¤å¤ç¨æ·åå¯ä»¥æ´æ¹ï¼ä½å¿
须大åè±æ
and c.object_type='TABLE') a
left join
(select a.owner,a.table_name,a.column_name,a.constraint_name from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name and b.constraint_type = 'P') d
on a.owner=d.owner and a.table_name=d.table_name and a.column_name=d.column_name
order by a.owner,a.table_name;
温馨提示:答案为网友推荐,仅供参考