统计系统中表的个数与查询所有表的字段信息
统计系统中表的个数与查询所有表的字段信息
--统计系统中表的个数：
--表描述字段：
--需要字段：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;
