查询表结构的sql文,如下
select base.val from
( select column_name as val, column_id as cid, 2000 + column_id as rid
from user_tab_columns
where table_name ='@'
union all
select data_type as val, column_id as cid, 3000 + column_id as rid
from user_tab_columns
where table_name ='@'
union all
select case when data_scale is null then to_char(char_col_decl_length)
else '(' || to_char(data_precision) || ',' || to_char(data_scale) || ')'
end as val, column_id as cid, 4000 + column_id as rid
from user_tab_columns
where table_name ='@'
union all
select case when nullable = 'Y' then null
else 'NOT NULL'
end as val, column_id as cid, 5000 + column_id as rid
from user_tab_columns
where table_name ='@' ) base order by base.cid, base.rid
把@换成表的名字就可以了
user_tab_columns中各个字段的含义可以参看下面的地址
http://ss64.com/orad/USER_TAB_COLUMNS.html
USER_TAB_COLUMNS
Columns of user's tables, views and clusters
Columns
___________________________
TABLE_NAME
Table,view or cluster name
COLUMN_NAME
Column name
DATA_TYPE
Datatype of the column
DATA_TYPE_MOD
Datatype modifier of the column
DATA_TYPE_OWNER
Owner of the datatype of the column
DATA_LENGTH
Length of the column in bytes
DATA_PRECISION
Length: decimal digits (NUMBER) or binary digits (FLOAT)
DATA_SCALE
Digits to right of decimal point in a number
NULLABLE
Does column allow NULL values?
COLUMN_ID
Sequence number of the column as created
DEFAULT_LENGTH
Length of default value for the column
DATA_DEFAULT
Default value for the column
NUM_DISTINCT
The number of distinct values in the column
LOW_VALUE
The low value in the column
HIGH_VALUE
The high value in the column
DENSITY
The density of the column
NUM_NULLS
The number of nulls in the column
NUM_BUCKETS
The number of buckets in histogram for the column
LAST_ANALYZED
The date of the most recent time this column was analyzed
SAMPLE_SIZE
The sample size used in analyzing this column
CHARACTER_SET_NAME
Character set name
CHAR_COL_DECL_LENGTH
Declaration length of character type column
GLOBAL_STATS
Are the statistics calculated without merging underlying partitions?
USER_STATS
Were the statistics entered directly by the user?
AVG_COL_LEN
The average length of the column in bytes
CHAR_LENGTH
The maximum length of the column in characters
CHAR_USED
C is maximum length given in characters,B if in bytes
V80_FMT_IMAGE
Is column data in 8.0 image format?
DATA_UPGRADED
Has column data been upgraded to the latest type version format?