ORALCE 统计数据库表结构，按照表结构文档输出

select DISTINCT t1.comments,t1.table_name,t2.column_name,t2.comments,

CASE WHEN t.DATA_TYPE='TIMESTAMP(6)' or t.DATA_TYPE='DATE' then

t.DATA_TYPE

else

T.DATA_TYPE||'('||t.DATA_LENGTH||')' end type,T.NULLABLE from user_tab_comments t1 LEFT JOIN

user_tab_columns t ON T.TABLE_NAME = T1.table_name

LEFT JOIN user_col_comments T2 ON T1.table_name = T2.table_name AND T.COLUMN_NAME = T2.column_name

where T1.table_name like 'XXXX%' ORDER BY T1.table_name ;

输出结果如下：

 

说明：

user_tables：TABLE_NAME,TABLESPACE_NAME,LAST_ANALYZED等

dba_tables：ower,table_name,tablespace_name,last_analyzed等

all_tables：ower,table_name,tablespace_name,last_analyzed等

all_objects：ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等

ALL_OBJECTS describes all objects accessible to the current user. 描述当前用户有访问权限的所有对象

DBA_OBJECTS describes all objects in the database. 描述了数据库中的所有对象

USER_OBJECTS describes all objects owned by the current user. 描述了当前用户所拥有的所有对象

user_tab_comments;表注释

表字段

user_tab_columns

all_tab_columns

dba_tab_columns

字段注释

user_col_comments

dba_col_comments，

all_col_comments