oracle查询表的字段信息和主键信息

–字段信息

select A.TABLE_NAME,A.COLUMN_ID,A.COLUMN_NAME,
case A.DATA_TYPE 
when 'VARCHAR2' then A.DATA_TYPE||'('||A.DATA_LENGTH||')' 
when 'NUMBER' then A.DATA_TYPE||'('||A.DATA_PRECISION||','||A.DATA_SCALE||')'
else A.DATA_TYPE
end DATA_TYPE
,C.COMMENTS
from user_tab_columns A
left join user_col_comments C
on A.TABLE_NAME=C.TABLE_NAME and A.COLUMN_NAME=C.COLUMN_NAME
--where A.owner='xxx' and A.table_name like 'xxx%'  
order by A.table_name,A.COLUMN_ID;

–主键信息

select cu.TABLE_NAME,utc.COMMENTS,cu.CONSTRAINT_NAME,cu.column_name 
from user_cons_columns cu,
 user_constraints au,
 user_tab_comments utc
where cu.constraint_name=au.constraint_name 
and cu.TABLE_NAME=utc.TABLE_NAME
and cu.POSITION=1
order by cu.table_name;