ORACLE 取得 table 資訊

ORACLE 取得 table 資訊

select c.table_name,c.column_name,c.data_type,c.data_length,tpk.pk,tfk.fk,c.nullable
from all_tab_cols c,
(select c2.table_name,c1.column_name ,'TRUE' "PK"
from All_cons_columns c1 , all_constraints c2
where c1.owner = &owna
and c1.constraint_name = c2.constraint_name
and c2.constraint_type = 'P') tPK,
(select c2.table_name,c1.column_name ,'TRUE' "FK"
from All_cons_columns c1 , all_constraints c2
where c1.owner = &owna
and c1.constraint_name = c2.constraint_name
and c2.constraint_type = 'R') tFK
where (c.table_name = tpk.table_name(+) and c.column_name = tpk.column_name(+))
and (c.table_name = tFK.table_name(+) and c.column_name = tfk.column_name(+))
and c.owner = &owna
order by 1 ,2