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

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

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

1. 列出特定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 

   ON 

     C.OWNER = T.OWNER AND C.TABLE_NAME = T.TABLE_NAME

   ON

     C.OWNER = R.Owner AND 

   AND 

     C.COLUMN_NAME = R.COLUMN_NAME

   WHERE  

     C.OWNER  = 'SCHEMA_NAME'

   BY C.TABLE_NAME, C.COLUMN_ID

   
   
2. 取得所有索引資料
   依資料表名稱、索引名稱、欄位順序、欄位名稱、排序方向(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 

   JOIN ALL_IND_COLUMNS C

   ON 

   AND

   
   

     I.INDEX_NAME = C.INDEX_NAME

   WHERE

     C.TABLE_OWNER = 'SCHEMA_NAME'

   BY I.TABLE_NAME, I.INDEX_NAME, COLUMN_POSITION

   
   
3. 取得主鍵值(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

   'SCHEMA_NAME'

   BY C.TABLE_NAME, D.POSITION

SELECT 列出 Table 的所有欄位

以往在查詢某個 Table 所有欄位時, 都會用 select * from tableName, 如下 :


但有時候, 要取得所有欄位, 卻偏偏要對某幾個欄位做特別處理,

這時候就要一一列出所有欄位名稱,

若 Table 欄位很多時, 這是一個令人很煩的事情,

以下介紹利用 Oracle SQL Developer 如何快速列出 Table 所有欄位,

參考如下 :

1) 輸入 " SELECT
tableAliasName.", 這時候, 會自動彈出該 Table 所有欄位的清單 :


2) 選擇第一個欄位, 按 <Shift>, 然後選擇最後一個欄位, 再按 <Enter> 即可取回 :


3) 取回結果一整串, 實在太長, 不易閱讀 :



4) 選擇整個 Select SQL 後, 按 <Ctrl> + <F7> 即可進行 格式化, 最後結果, 如下 :


以上, 這樣就可快速建立列出所有欄位了.

昭佑.天翔