select?a.column_id?as?列号,?a.column_name?as?列名,?a.data_type?as?类型,?
??decode(a.data_type,'NUMBER',a.data_precision,a.data_length)?as?长度,?a.data_scale?as?小数位,
??decode(e.uniqueness,'UNIQUE','Y','N')?as?是否是唯一的,?decode(e.key,'Y','Y','N')?是否是主键,
??f.comments?as?注释,?a.nullable?as?是否允许空,?a.data_default?as?默认值
from?user_tab_columns?a,?user_col_comments?f,
(select?b.table_name,?b.index_name,b.uniqueness,?c.column_name,
?????????decode(d.constraint_name,NULL,'N','Y')?key
??from?user_indexes?b,?user_ind_columns?c,
??????(?select?constraint_name?from?user_constraints?where?constraint_type='P'?)?d
??where?b.index_name=c.index_name?and?b.index_name=d.constraint_name(+)?)?e
where?a.table_name='TEMPTABLE'?and?a.table_name=e.table_name(+)?and?a.column_name=e.column_name(+)
??and?a.table_name=f.table_name?and?a.column_name=f.column_name
order?by?a.column_id
?
select?TC.table_name?,?TC.column_name?,TC.DATA_TYPE,TC.data_length,TC.data_precision,TC.nullable,
TC.char_col_decl_length,
decode(C.constraint_type,'P','PK','U','Unique','R','FK','C','Check',C.constraint_type)
from?USER_TAB_COLUMNS?TC,USER_CONS_COLUMNS?CC?,user_constraints?C
where?TC.table_name?=?'EMP'
and?TC.table_name?=?CC.table_name(+)
and?TC.column_name?=?CC.column_name?(+)
and?CC.constraint_name?=?C.constraint_name(+)
?
select????A.column_name?字段名,A.data_type?数据类型,A.data_length?长度,A.data_precision?
整数位,????A.Data_Scale?小数位,A.nullable?允许空值,A.Data_default?缺省值,B.comments?
备 注?from??user_tab_columns?A,user_col_comments?B?where?a.COLUMN_NAME=b.column_name?and????A.Table_Name?=?B.Table_Name?and?A.Table_Name='TEST'?
select?table_name?from?user_tables;?//当前用户的表?
select?table_name?from?all_tables;?//所有用户的表?
select?table_name?from?dba_tables;?//包括系统表?
select?table_name?from?dba_tables?where?owner='用户名'?
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等?
获取表字段：
select?*?from?user_tab_columns?where?Table_Name='用户表';?
select?*?from?all_tab_columns?where?Table_Name='用户表';?
select?*?from?dba_tab_columns?where?Table_Name='用户表';?
user_tab_columns：?
table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等?
all_tab_columns?：?
ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等?
dba_tab_columns：?
ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等?
获取表注释：
select?*?from?user_tab_comments?
user_tab_comments：table_name,table_type,comments?
--相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。?
获取字段注释：
select?*?from?user_col_comments?
user_col_comments：table_name,column_name,comments
