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

select a.TABLE_NAME as 表英文名,b.COMMENTS as 表中文名,b.COMMENTS as 表业务描述,a.num_rows as 表记录数from user_tables a, user_tab_comments bWHERE a.TABLE_NAME = b.TABLE_NAMEorder 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 aleft 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'  ) don 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_NAMEorder by a.table_name