oracle+统计表的字段,统计系统中表的个数与查询所有表的字段信息-Oracle

统计系统中表的个数与查询所有表的字段信息

–统计系统中表的个数：

–表描述字段：

–需要字段：Name、Code、Commente

select lower(a.TABLE_NAME) || ‘-‘ || b.comments,

lower(a.TABLE_NAME),

b.comments

from user_tables a, user_tab_comments b

where a.TABLE_NAME = b.TABLE_NAME

order by b.comments,a.TABLE_NAME;

–查询所有表的字段信息。

–Name,Code,Data Type,Length ,P ,F,comments

select a.table_name,

t.comments,

a.COLUMN_ID,

nvl(b.comments,b.column_name),

b.column_name,

a.DATA_TYPE,

case

when a.DATA_TYPE = ‘VARCHAR2’ then

a.DATA_TYPE || ‘(‘ || a.DATA_LENGTH || ‘)’

when a.DATA_TYPE = ‘DATE’ then

”

when a.DATA_TYPE = ‘NUMBER’ and a.DATA_SCALE is not null then

a.DATA_TYPE || ‘(‘ || a.DATA_PRECISION || ‘,’ || a.DATA_SCALE || ‘)’

when a.DATA_TYPE = ‘NUMBER’ and a.DATA_SCALE is null then

a.DATA_TYPE || ‘(‘ || a.DATA_LENGTH || ‘)’

else

”

end case,

case

when (select nvl(count(1), 0)

from user_cons_columns c, user_constraints d

where d.constraint_name = c.constraint_name

and d.constraint_type = ‘P’

and c.table_name = a.table_name

and c.column_name = a.column_name) = 1 then

nvl((select d.constraint_name

from user_cons_columns c, user_constraints d

where d.constraint_name = c.constraint_name

and d.constraint_type = ‘P’

and c.table_name = a.table_name

and c.column_name = a.column_name),”)

else

”

end case

from user_tab_cols a, user_col_comments b,user_tab_comments t

where a.TABLE_NAME = b.table_name

and a.COLUMN_NAME = b.column_name

and a.table_name=t.table_name

order by a.table_name,a.COLUMN_ID;