[Tools] 自動化產出個人風格 SQL Server 資料表文件

2016-07-07

13335
0
Tools
2016-07-19

在專案執行過程中，無法即時維護最新版文件是大家共同的瓶頸，因此若能以加註資料表及欄位描述的方式自動產出文件，就能有效解決文件不斷更新的問題；本文透過自動化工具產出資料表文件，不僅能避掉繁瑣的人工作業，又能依據樣板產出不同風格樣式，大家可以嘗試看看喔!

前言

在系統開發上，最可靠的資料庫結構就是目前正在使用的那個，而你手頭上那份修修補補的文件有很大的機會是過時的，因此最好是在資料庫中就說明一切(包括資料表欄位命名及設計)，並在資料表及欄位中加註描述(Description)，讓開發接手的人有跡可循，就如同最好的說明文件就是程式本身的道理相同。

不可諱言地資料表說明文件還是有其必要性，因為可以作為快速資料表查閱之參考，另外在專案開發上也需要提供資料表文件來完成交付作業，但此時難道只能靠自己手工撰寫嗎? 既然所有資訊都已經存在資料庫了，我們當然就可以直接交給程式來處理，不應該浪費時間在乏味的抄寫比對上，因此筆者開發了個 DB 文件產生器工具，並可依據樣板產出不同樣式的文件，來滿足不同公司所需的文件風格。以下說明。

設計構想

其實程式構想很簡單，就是透過 SchemaInformation 相關資料表串出所需資訊，以下為此文件產生器所使用的SQL語句；由於筆者盡可能地將需資訊都一次串出，因此沒有考量過多效能調教上的問題，所以如果有可以精進的地方再麻煩不吝指導一下囉。

SELECT 
   tb.TABLE_NAME AS 'TableName'
  ,CASE 
    WHEN tb.TABLE_TYPE = 'VIEW'
    THEN (SELECT value 
            FROM sys.fn_listextendedproperty(NULL, 'user', 'dbo', 'view', tb.TABLE_NAME, DEFAULT, DEFAULT)
            WHERE name = 'MS_Description'
            AND objtype = 'VIEW')
    ELSE (SELECT value 
            FROM sys.fn_listextendedproperty(NULL, 'user', 'dbo', 'table', tb.TABLE_NAME, DEFAULT, DEFAULT)
            WHERE name = 'MS_Description'
            AND objtype = 'TABLE')
   END AS 'TableDescription'
  ,tb.TABLE_TYPE AS 'TableType'
  ,col.ORDINAL_POSITION AS 'No'
  ,col.COLUMN_NAME AS 'ColumnName'
  ,col.DATA_TYPE AS 'DataType'
  ,CASE 
    WHEN col.CHARACTER_MAXIMUM_LENGTH = -1
      THEN 'MAX' 
      ELSE LTRIM(STR(col.CHARACTER_MAXIMUM_LENGTH,10))
   END AS 'Length'
  ,col.NUMERIC_SCALE AS 'NumericScale'
  ,col.NUMERIC_PRECISION AS 'NumericPrecision'
  ,col.COLUMN_DEFAULT AS 'Default'
  ,col.IS_NULLABLE AS 'Nullable'   
  ,CASE 
    WHEN COLUMNPROPERTY(object_id(tb.TABLE_NAME), col.COLUMN_NAME, 'IsIdentity') = 1  
      THEN 'YES' 
      ELSE 'NO' 
   END AS 'Identity'  
  ,(SELECT value
      FROM sys.fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', tb.TABLE_NAME, 'column', DEFAULT)
      WHERE name = 'MS_Description'
      AND objtype = 'COLUMN'
      AND objname COLLATE Chinese_Taiwan_Stroke_CI_AS = col.COLUMN_NAME) AS 'Description'
  ,CASE 
    WHEN tbc.CONSTRAINT_NAME is not null  
      THEN 'YES' 
      ELSE 'NO' 
   END AS 'PK' 
  ,tbc.CONSTRAINT_NAME AS ' PkConstraint' 
  ,CASE 
    WHEN kcu1.CONSTRAINT_NAME is not null  
      THEN 'YES' 
      ELSE 'NO' 
   END AS 'FK'  
  ,kcu1.CONSTRAINT_NAME AS ' FkConstraint'
  ,kcu2.TABLE_NAME AS 'FkReferencedTable' 
  ,kcu2.COLUMN_NAME AS 'FkReferencedColumn'

FROM 

  INFORMATION_SCHEMA.TABLES tb
  LEFT JOIN INFORMATION_SCHEMA.COLUMNS col ON (tb.TABLE_NAME = col.TABLE_NAME)
  LEFT JOIN
  (
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu1 
      ON kcu1.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG  
      AND kcu1.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA 
      AND kcu1.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu2 
      ON kcu2.CONSTRAINT_CATALOG = rc.UNIQUE_CONSTRAINT_CATALOG  
      AND kcu2.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA 
      AND kcu2.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME 
      AND kcu2.ORDINAL_POSITION = kcu1.ORDINAL_POSITION 
  ) ON (tb.TABLE_NAME = kcu1.TABLE_NAME AND col.COLUMN_NAME = kcu1.COLUMN_NAME)
  LEFT JOIN
  (
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tbc
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS colc 
    ON  colc.CONSTRAINT_NAME = tbc.CONSTRAINT_NAME
    AND colc.TABLE_NAME = tbc.TABLE_NAME
    AND tbc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
  )  ON (tb.TABLE_NAME = tbc.TABLE_NAME AND col.COLUMN_NAME = colc.COLUMN_NAME)

ORDER BY tb.TABLE_NAME, col.ORDINAL_POSITION

執行後所有資訊都正確顯示

有了這些資料後，接著就需要將資料輸出至 Excel 中產生文件；由於筆者考量到每個專案客戶所需要產出文件的 Layout 都不盡相同，因此希望讓用戶可自行決定輸出樣式及欄位，所以選擇以樣板方式來設計產出文件，而樣板主要分為資料表清單(#TableListTemplate)及資料欄位資訊(#TableSchemaTemplate)頁籤，可以依照個人需求調整頁籤內資料呈現的方式，滿足各個專案客戶的文件需求。

資料表清單樣板