[Tool]Excel整理TableSchema

2011-12-29

1327
0
2012-01-02

摘要:[Tool]Excel整理TableSchema

column All.rar

簡略用法

<Ctrl+D下載欄位資訊 ; Ctrl+U 更新欄位資訊 ; ALT+F11開啟編輯畫面 ; 使用時巨集必定要開啟>

附上程式碼和Excel 文件使用時確認表格名稱欄位名稱欄位備註要對到

'''''''''''''''''''''''''


Sub 下載欄位定義()
   '建立資料庫連線

   Dim cnt As ADODB.Connection

   Dim rst As ADODB.Recordset

   Dim stCon As String '連線資訊
   Dim stSQL As String 'SQL字串

   Set cnt = New ADODB.Connection
   Set rst = New ADODB.Recordset

   stCon = "Provider=sqloledb;Server=192.168.1.1;Database=test; integrated security = sspi;" '連線資訊
   cnt.ConnectionString = stCon
   cnt.Open
       
    
   '資料select出來貼上
   
   stSQL = "SELECT * FROM (SELECT a.TABLE_NAME as 表格名稱,b.COLUMN_NAME as 欄位名稱,b.DATA_TYPE as 資料型別,b.CHARACTER_MAXIMUM_LENGTH  as 最大長度,b.COLUMN_DEFAULT as 預設值,b.IS_NULLABLE as 允許空值,(SELECT value FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table',a.TABLE_NAME, 'column', default)WHERE name='MS_Description'and objtype='COLUMN'and objname Collate Chinese_Taiwan_Stroke_CI_AS=b.COLUMN_NAME) as 欄位備註 FROM INFORMATION_SCHEMA.TABLES a LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON (a.TABLE_NAME=b.TABLE_NAME)WHERE TABLE_TYPE='BASE TABLE') aa ORDER BY 表格名稱"
   rst.Open stSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText
   
   rst.MoveFirst
       If Not rst.EOF Then
    
    ActiveSheet.Range("A2").CopyFromRecordset rst

    End If
    rst.Close  '關閉記錄集
    cnt.Close  '關閉資料庫連結,釋放資源
  End Sub

''''''''''''''''''''
Sub 更新欄位定義()  '執行SQL語句

   'On Error Resume Next
   'On Error GoTo Error_Handle
       
   '建立資料庫連線

   Dim cnt As ADODB.Connection
   Dim cmd As ADODB.Command
   
   '設定連線資訊
   Set cnt = New ADODB.Connection
   stCon = "Provider=sqloledb;Server=192.168.1.1;Database=test; integrated security = sspi;" '連線資訊
   cnt.ConnectionString = stCon
   cnt.Open
   
   '指令設定
   
   Set cmd = New ADODB.Command
   cmd.ActiveConnection = cnt '指令用的連線資訊
      
   '指令所要執行的SQL句
   For i = 2 To 100 '第2行到100行
   
   miketable = Cells(i, 1).Value
   mikecolumn = Cells(i, 2).Value
   mikecomment = Cells(i, 7).Value
   
   cmd.CommandText = "IF not exists(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '" & miketable & "', 'column', '" & mikecolumn & "')) exec sp_addextendedproperty 'MS_Description', '" & mikecomment & "', 'user', 'dbo', 'table', '" & miketable & "', 'column', '" & mikecolumn & "' ELSE exec sp_updateextendedproperty 'MS_Description', '" & mikecomment & "', 'user', 'dbo', 'table', '" & miketable & "', 'column', '" & mikecolumn & "' "
            
   If miketable = "" Then GoTo XX   '跳過Table空白
   If mikecomment = "" Then GoTo XX '跳過欄位定義空白
   cmd.Execute '執行查詢
XX:
   Next i
        
   '釋放空間
   Set cmd = Nothing
   cnt.Close
   Set cnt = Nothing
      
End Sub

回首頁