select column_name 列名, type 列类型, nullable 允许为空, isindex 是否主键,comments 列注释
from (select c.table_name,
c.column_name,
decode(data_type,
'VARCHAR2',
data_type || '(' || data_length || ')',
'CHAR',
data_type || '(' || data_length || ')',
'NUMBER',
data_type || '(' ||
decode(to_char(DATA_SCALE),
'0',
to_char(DATA_PRECISION),
DATA_PRECISION || ',' || DATA_SCALE) || ')',
data_type) type,
c.nullable,
d.comments,
(select MAX('Y')
from user_constraints g, user_cons_columns h
where g.constraint_name = h.constraint_name
and g.constraint_type='P'
and g.table_name = c.table_name
and h.column_name = d.column_name) isindex,
c.column_id
from user_tab_cols c, user_col_comments d
where c.column_name = d.column_name
and c.table_name = d.table_name
union all
select a.table_name,
'表:' || a.table_name || ' ' || b.comments,
null,
null,
null,
null,
-1
from user_tables a, user_tab_comments b
where a.table_name = b.table_name
union
select f.table_name,
'列名',
'列类型',
'允许为空',
'列注释',
'是否主键',
0
from user_tables f
union all
select g.table_name,
null,
null,
null,
null,
null,
999*h.levels
from user_tables g ,(select level as levels from dual connect by level<3) h) e
order by table_name, column_id;