查询Oracle数据库的表和字段类型和长度等信息

柔光的暖阳◎ 2021-12-10 10:39 975阅读 0赞

// 通过user\_tab\_columns & user\_col\_comments 查询所有表和列等属性

SELECT \*   
FROM user\_tab\_columns a, user\_col\_comments b  
 WHERE a.TABLE\_NAME = 'TEST02'  
   AND b.table\_name = 'TEST02';

所有属性如下：

TABLE\_NAME,COLUMN\_NAME,DATA\_TYPE,DATA\_TYPE\_MOD,DATA\_TYPE\_OWNER,DATA\_LENGTH,DATA\_PRECISION,DATA\_SCALE,NULLABLE,COLUMN\_ID,DEFAULT\_LENGTH,DATA\_DEFAULT,NUM\_DISTINCT,LOW\_VALUE,HIGH\_VALUE,DENSITY,NUM\_NULLS,NUM\_BUCKETS,LAST\_ANALYZED,SAMPLE\_SIZE,CHARACTER\_SET\_NAME,CHAR\_COL\_DECL\_LENGTH,GLOBAL\_STATS,USER\_STATS,AVG\_COL\_LEN,CHAR\_LENGTH,CHAR\_USED,V80\_FMT\_IMAGE,DATA\_UPGRADED,HISTOGRAM,TABLE\_NAME\_1,COLUMN\_NAME\_1,COMMENTS

// 通过user\_tab\_columns & user\_col\_comments 查询字段的类型和长度信息

SELECT a.TABLE\_NAME, b.COLUMN\_NAME, a.DATA\_TYPE, a.CHAR\_USED, a.DATA\_LENGTH, a.CHAR\_LENGTH  
FROM user\_tab\_columns a, user\_col\_comments b  
 WHERE a.TABLE\_NAME = 'TEST02'   
 AND b.table\_name = 'TEST02';

属性说明：

完整的属性说明参考官方文档：[https://docs.oracle.com/cd/B19306\_01/server.102/b14237/statviews\_2094.htm][https_docs.oracle.com_cd_B19306_01_server.102_b14237_statviews_2094.htm]

<table style="width:682px;"> 
 <tbody> 
  <tr> 
   <td style="width:132px;">TABLE_NAME</td> 
   <td style="width:547px;">Name of the table, view, or cluster</td> 
  </tr> 
  <tr> 
   <td style="width:132px;">COLUMN_NAME</td> 
   <td style="width:547px;">Column name</td> 
  </tr> 
  <tr> 
   <td style="width:132px;">DATA_TYPE</td> 
   <td style="width:547px;">Datatype of the column</td> 
  </tr> 
  <tr> 
   <td style="width:132px;">CHAR_USED</td> 
   <td style="width:547px;"><code>B</code>&nbsp;|&nbsp;<code>C</code>.&nbsp;<code>B</code>&nbsp;indicates that the column uses&nbsp;<code>BYTE</code>length semantics.&nbsp;<code>C</code>&nbsp;indicates that the column uses&nbsp;<code>CHAR</code>&nbsp;length semantics.&nbsp;<code>NULL</code>&nbsp;indicates the datatype is not any of the following: 
    <ul> 
     <li> <p><code>CHAR</code></p> </li> 
     <li> <p><code>VARCHAR2</code></p> </li> 
     <li> <p><code>NCHAR</code></p> </li> 
     <li> <p><code>NVARCHAR2</code></p> </li> 
    </ul></td> 
  </tr> 
  <tr> 
   <td style="width:132px;">DATA_LENGTH</td> 
   <td style="width:547px;">ength of the column (in bytes)</td> 
  </tr> 
  <tr> 
   <td style="width:132px;">CHAR_LENGTH</td> 
   <td style="width:547px;">Displays the length of the column in characters. This value only applies to the following datatypes: 
    <ul> 
     <li> <p><code>CHAR</code></p> </li> 
     <li> <p><code>VARCHAR2</code></p> </li> 
     <li> <p><code>NCHAR</code></p> </li> 
     <li> <p><code>NVARCHAR</code></p> </li> 
    </ul></td> 
  </tr> 
 </tbody> 
</table>

[https_docs.oracle.com_cd_B19306_01_server.102_b14237_statviews_2094.htm]: https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm