使用Excel將SQL Server 資料表變成資料表規格文件
1. 前言
本文介紹如何使用Excel的資料匯入及VBA，反向從SQL Server 將現有資料表變成資料表規格的文件。
2. 使用工具說明：
l Excel版本：2019
l SQL Server的資料庫。
l Excel開啟「開發人員」的索引標籤：「檔案」→「選項」→「自訂功能區」→「主要索引標籤」，選取「開發人員」進行新增。
3. Excel從SQL Server資料庫匯入資料
(1) 匯入資料表名稱
l 首先點選「資料」→「取得資料」→「從資料庫」→「從SQL Server資料庫」
l 接著輸入資料庫連結的相關資訊，並點選「進階選項」在SQL陳述式中，輸入以下SQL語法。
SQL語法：
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
order by TABLE_NAME;
l 點選 確定 → 接著點選 載入
l 執行成功後，會將所有資料表名稱匯入新的Sheet中。
(2) 匯入各Table的Schema資料
l 接著要匯入各Table的Schema資料。作法同上「(1)匯入資料表名稱」，但輸入的SQL語法如下。
SQL語法：
SELECT
tb.TABLE_NAME AS 'TableName'
,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.COLUMN_DEFAULT AS 'Default'
,col.IS_NULLABLE AS 'Nullable'
,CASE
WHEN tbc.CONSTRAINT_NAME is not null
THEN 'V'
ELSE ''
END AS 'PK'
,tbc.CONSTRAINT_NAME AS ' PkConstraint'
,(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 'Comments'
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;
l 最後匯出結果：
4. 修改匯入資料表的格式
(1) 將資料篩選功能取消
l 選擇隨意欄位→「資料」→「篩選」，資料表名稱及各Table的Schema資料，皆要執行。
(2) 修改工作表名稱
l 資料表名稱，改為「TABLE_LIST」
l 各Table的Schema資料，改為「DETAIL」
l 並將其他未使用到的工作表刪除。
5. 建立巨集的程式
(1) 將各Table的Schema資料，分開成各Sheet
l 點選「開發人員」→「巨集」，輸入巨集名稱「table_Schema」，點選 建立 。
l 在VBA程式編輯器中，輸入以下程式碼，完成後存檔並關閉VBA程式編輯器。
程式碼：
'Ed為跑回圈次數、St為第一欄的資料、TableName為選擇的Table名稱
Dim Ed As Integer
Dim St As Integer
Dim TableName As String
Ed = ActiveSheet.Range("A2").End(xlDown).Row
St = 2
Do While St <= Ed
Sheets("TABLE_LIST").Select
'設定TableName
TableName = Range("A" & St).Value
'到Detail進行資料篩選的查詢
Sheets("DETAIL").Select
'選取所有detail的資料，lastAddress為最後一筆資料的位置
Range("A1").Select
DetailDown = ActiveSheet.Range("A1").End(xlDown).Row
DetailRight = ActiveSheet.Range("A1").End(xlToRight).Address
lastAddress = Left(DetailRight, 3) & DetailDown
Selection.AutoFilter
ActiveSheet.Range("$A$1:" & lastAddress).AutoFilter Field:=1, Criteria1:= _
TableName
'選取查詢後的資料
Range("B1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'新增新的工作表
Sheets.Add After:=ActiveSheet
'重新命名工作表明稱為TableName
ActiveSheet.Name = TableName
'於A4貼上資料、於A3輸入TableName
Range("A4").Select
ActiveSheet.Paste
Range("A3").Value = TableName
Selection.Columns.AutoFit
'於A1新增回TABLE_LIST的超連結
Range("A1").Value = "TABLE_LIST"
Range("A1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"TABLE_LIST!A1", TextToDisplay:="TABLE_LIST"
'回到DETAIL將資料篩選關掉
Sheets("DETAIL").Select
Selection.AutoFilter
'回到TABLE_LIST
Sheets("TABLE_LIST").Select
St = St + 1
Loop
(2) Table_List建立與各Table資料的超連結
l 點選「開發人員」→「巨集」，輸入巨集名稱「table_list_link」，點選 建立 。
l 在VBA程式編輯器中，輸入以下程式碼，完成後存檔並關閉VBA程式編輯器。
程式碼：
'Ed為跑回圈次數、St為第一欄的資料、TableName為選擇的Table名稱
Dim Ed As Integer
Dim St As Integer
Dim TableName As String
Ed = ActiveSheet.Range("A2").End(xlDown).Row
St = 2
Do While St <= Ed
'選擇TABLE_LIST的sheet
Sheets("TABLE_LIST").Select
'設定TableName
TableName = Range("A" & St).Value
'選取要設定超連結的欄位
Range("A" & St).Select
'設定超連結
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & St), Address:="", SubAddress:= _
Sheets(TableName).Name & "!A1", TextToDisplay:=Sheets(TableName).Name
St = St + 1
Loop
6. 執行巨集結果
(1) 執行table_Schema的巨集
l 點選「開發人員」→「巨集」，點選「table_Schema」，點選 執行 。
l 執行結果：產出各Table Schema的Sheet資料。
(2) 執行table_list_link的巨集
l 點選「開發人員」→「巨集」，點選「table_list_link」，點選 執行 。
l 執行結果：各Table 點選後，可以連結到相對應的Table Schema資料。
7. 測試結果
l 點選「County」連結到相對應的Table Schema資料。
l 點選後結果。
l 若點選「TABLE_LIST」會回到TABLE_LIST的Sheet。
8. 參考來源
l 邊界與列數、行數、位址
http://cymail6.blogspot.com/2012/10/blog-post_1665.html
l Excel VBA 尋找表格最後一欄、最後一列、最右下角教學與範例
https://officeguide.cc/excel-vba-find-row-column-cell-tutorial-examples/
l [Tools] 自動化產出個人風格 SQL Server 資料表文件
