在寫管理專案資料庫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 
    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

  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 
      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

  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
      C.CONSTRAINT_TYPE = 'P' AND C.OWNER = 'SCHEMA_NAME'
    ORDER BY C.TABLE_NAME, D.POSITION

Comments

# by jain

謝謝,趕快筆記~

# by AndyYang

多蝦多蝦

Post a comment