1,查询所有表和表注释:
SQL> select t.TABLE_NAME ,t.LAST_ANALYZED,s.COMMENTS from user_all_tables t , user_tab_comments s where t.table_name = s.table_name;
TABLE_NAME LAST_ANALYZED COMMENTS
------------------------------ ------------- --------------------------------------------------------------------------------
CITY 2015/2/11 22: 城市信息表
COLLEGE_NEWS 2015/2/11 22: 学院新闻
2,查询某张表所有列和其他信息:
select
t.COLUMN_NAME,t.DATA_LENGTH,c.COMMENTS ,t.DATA_TYPE,t.DATA_PRECISION,t.DATA_SCALE,t.DATA_DEFAULT,t.NULLABLE,t.LAST_ANALYZED
from
user_tab_columns t,user_col_comments c where t.table_name = c.table_name
and t.column_name = c.column_name and t.table_name = 'CITY';
COLUMN_NAME DATA_LENGTH COMMENTS DATA_TYPE DATA_PRECISION DATA_SCALE DATA_DEFAULT NULLABLE LAST_ANALYZED
------------------------------ ----------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------- ---------- -------------------------------------------------------------------------------- -------- -------------
ID 22 主键ID NUMBER 19 0 N 2015/2/11 22:
PARENT_ID 22 父节点ID NUMBER 19 0 Y 2015/2/11 22:
NAME 80 名称 VARCHAR2 Y 2015/2/11 22:
CREATE_BY 22 创建人 NUMBER 19 0 Y 2015/2/11 22:
CREATE_TIME 7 创建时间 DATE Y 2015/2/11 22:
LAST_UPDATE_BY 22 最后修改人 NUMBER 19 0 Y 2015/2/11 22:
LAST_UPDATE_TIME 7 最后修改时间 DATE Y 2015/2/11 22:
ENABLE_FLAG 22 标识符 NUMBER 1 0 Y 2015/2/11 22: