Oracle查询表英文名、表中文名、表业务描述、表记录数、字段数量

查询表英文名、表中文名、表业务描述、表记录数

select a.TABLE_NAME as 表英文名,
b.COMMENTS as 表中文名,
b.COMMENTS as 表业务描述,
a.num_rows as 表记录数
from user_tables a, user_tab_comments b
WHERE a.TABLE_NAME = b.TABLE_NAME
order by a.TABLE_NAME;

查询字段数量

select table_name,count(table_name) from user_tab_columns group by table_name order by TABLE_NAME;

如果真的想彻底删除这个表，可以使用下面的命令清空回收站

PURGE RECYCLEBIN;

查询表主键

select a.table_name,
 a.column_name
 from user_cons_columns a, 
user_constraints b 
where a.constraint_name = b.constraint_name and b.constraint_type = 'P' 

查询是否主键

select 
    a.owner,
    a.table_name,
    a.column_name,
    a.data_type,  
    d.constraint_type,
    a.num_nulls 
from all_tab_columns a
left join 
 (    select 
        b.owner,
        b.TABLE_NAME,
        column_name,
        constraint_type 
      from all_cons_columns  b
     join all_constraints c
      on b.CONSTRAINT_NAME=c.CONSTRAINT_NAME
      and b.table_name=c.table_name
      and b.owner=c.owner
      and c.constraint_type='P'  --type为P表示为主键值，还有关于外键等
and b.owner='TEST'
  ) d
on a.owner=d.owner 
and a.table_name=d.table_name 
and a.column_name=d.column_name 
where 1=1 
and a.owner='TEST'
--  and a.table_name='CN_AREA' 
-- and a.owner in ('AQDFCS','SDVD')

--查询表英文名、字段英文名、字段中文名、字段业务描述、字段类型（长度）、是否必填

SELECT 
a.table_name 表英文名,
b.column_name 字段英文名,
       a.comments    字段中文名,
       a.comments    字段业务描述,
       b.data_type || '(' || b.data_length || ')'  字段类型,
--        b.NULLABLE    是否必填,
decode(b.NULLABLE,'Y','否','是')  
decode
  FROM user_col_comments a, all_tab_columns b
 where a.table_name = b.table_name
   and a.COLUMN_NAME = b.COLUMN_NAME
order by a.table_name