筆記-取得Oracle資料表、欄位、索引、主鍵值資訊

2011-06-18 10:15 AM

在寫管理專案資料庫Schema的輔助工具，其中有個功能是由資料庫取得資料表清單以及資料表欄位、索引、Primary Key等資訊，以便跟文件記載內容比對，挑出文件與實際狀況不符的部分，提醒SA人員修正文件，或是追查Schema被他人異動過。

資料庫多半有系統資料表或檢視可查到Table、Column、Index等資訊，過去只研究過SQL Server，這回把Oracle的Schema資訊查詢語法也整理起來，寫成筆記備忘:

列出特定Schema下所有資料表及欄位型別、長度、精準位數、NULLABLE、預設值
ALL_TAB_COLUMNS包含了Table、View、Cluster的欄位資料(我還發現一些類似"BIN$o8LDCTzwFHuxoWTFo748sg==$0"系統隨機產生的TABLE_NAME)，所以我加上JOIN ALL_TABLES過濾。另外，由DATA_DEFAULT欄位意外學到LONG這個化石級的資料型別，處理起來超麻煩，連要TO_LOB()都還得配合INSERT INTO ... SELECT，讓我想到SQL 6.5時代要用TEXTPTR才能操作TEXT的不堪往事。所幸在ADO.NET中，row[“DATA_DEFAULT”].ToString()就成了，易如吹灰。
```
SELECT
```
```
  C.OWNER, C.TABLE_NAME, C.COLUMN_ID, C.COLUMN_NAME, 
```
```
  DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_DEFAULT, 
```
```
  NULLABLE, COMMENTS
```
```
FROM
```
```
  ALL_TAB_COLUMNS C 
```
```
JOIN ALL_TABLES T ON 
```
```
  C.OWNER = T.OWNER AND C.TABLE_NAME = T.TABLE_NAME
```
```
LEFT JOIN ALL_COL_COMMENTS R ON
```
```
  C.OWNER = R.Owner AND 
```
```
  C.TABLE_NAME = R.TABLE_NAME AND 
```
```
  C.COLUMN_NAME = R.COLUMN_NAME
```
```
WHERE  
```
```
  C.OWNER  = 'SCHEMA_NAME'
```
```
ORDER BY C.TABLE_NAME, C.COLUMN_ID
```

取得所有索引資料
依資料表名稱、索引名稱、欄位順序、欄位名稱、排序方向(ASC/DESC)列出所有索引項目

SELECT

  I.TABLE_OWNER, I.TABLE_NAME, I.INDEX_NAME, I.INDEX_TYPE,

  I.UNIQUENESS, C.COLUMN_POSITION, C.COLUMN_NAME, C.DESCEND

FROM

  ALL_INDEXES I JOIN ALL_IND_COLUMNS C

ON

  I.TABLE_OWNER = C.TABLE_OWNER AND

  I.INDEX_NAME = C.INDEX_NAME

WHERE

  C.TABLE_OWNER = 'SCHEMA_NAME'

ORDER BY I.TABLE_NAME, I.INDEX_NAME, COLUMN_POSITION

取得主鍵值(Primary Key)欄位

SELECT

  C.OWNER, C.TABLE_NAME, D.POSITION, D.COLUMN_NAME

FROM

  ALL_CONSTRAINTS C JOIN ALL_CONS_COLUMNS D

ON

  C.OWNER = D.OWNER AND

  C.CONSTRAINT_NAME = D.CONSTRAINT_NAME

WHERE

  C.CONSTRAINT_TYPE = 'P' AND C.OWNER = 'SCHEMA_NAME'

ORDER BY C.TABLE_NAME, D.POSITION

Comments