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

--统计系统中表的个数：
--表描述字段：
--需要字段：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;