筆記-取得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