運用 SQL-DMO 與 Transact-SQL 實現 SQL Server 資料庫物件的動態建立與管理

SQL-DMO (Distributed Management Objects) 自動化部署資料庫結構

SQL-DMO 為 SQL Server 7.0/2000/2005 (相容模式) 提供的 COM 自動化介面，允許用戶端程式 (VB6, VBScript, C++, C# via Interop) 以物件導向方式管理伺服器、資料庫、資料表、索引、作業、警示等。現代替代方案：SMO (SQL Server Management Objects) for .NET / PowerShell / T-SQL 腳本。

核心物件模型

• SQLServer：根物件，負責連線 (`Connect`)、執行即時命令 (`ExecuteImmediate`)、列舉資料庫/登入/裝置。
• Database：`Create`、`Drop`、`ExecuteNonQuery`、`Tables`、`StoredProcedures` 集合。
• Table：`Columns`、`Indexes`、`Keys` (PrimaryKey, ForeignKeys)、`Triggers`、`Permissions`。
• Column：`DataType`、`Length`、`Nullable`、`Identity`、`DefaultValue`、`ComputedText`。
• Index：`IndexType` (Clustered/NonClustered)、`IndexKeyType` (Unique/PrimaryKey)、`FillFactor`、`FileGroup`。

Visual Basic 6.0 完整範例：建立裝置、資料庫、資料表、索引、主鍵

' 專案參考：Microsoft SQLDMO Object Library (SQLDMO.rll / sqldmo.dll)
Dim oServer As New SQLDMO.SQLServer
Dim sTransQL As String

' 1. 連線伺服器
oServer.Connect ServerName:="(local)", Login:="sa", Password:="your_password"

' 2. 建立資料庫裝置 (SQL 7.0/2000 語法，現代版本建議直接 CREATE DATABASE 指定檔案路徑)
sTransQL = "USE master; " & _
           "DISK INIT NAME='Device_1', PHYSNAME='D:\SQL\Data\device1.dat', VDEVNO=100, SIZE=102400; " & _  -- 100 MB
           "DISK INIT NAME='Device_2', PHYSNAME='D:\SQL\Data\device2.dat', VDEVNO=101, SIZE=204800;"   -- 200 MB
oServer.ExecuteImmediate Command:=sTransQL, ExecuteType:=SQLDMOExec_Default

' 3. 建立資料庫於指定裝置
oServer.ExecuteImmediate "CREATE DATABASE Db_1 ON Device_1 = 100", SQLDMOExec_Default  -- 100 MB on Device_1
oServer.ExecuteImmediate "CREATE DATABASE Db_2 ON Device_2 = 100", SQLDMOExec_Default  -- 100 MB on Device_2

' 4. 在 Db_1 建立資料表 T_1
sTransQL = "USE Db_1; " & _
           "CREATE TABLE T_1 ( " & _
           "    Name VARCHAR(8) NULL, " & _
           "    Age TINYINT NULL, " & _
           "    Sex BIT NULL, " & _
           "    ID_Code CHAR(16) NOT NULL, " & _
           "    CONSTRAINT PK_T_1 PRIMARY KEY CLUSTERED (ID_Code) " & _
           "); " & _
           "CREATE INDEX IX_T_1_Name ON T_1 (Name);"  -- 非叢集索引
oServer.ExecuteImmediate sTransQL, SQLDMOExec_Default

' 5. 在 Db_2 建立資料表 T_2 (複合主鍵)
sTransQL = "USE Db_2; " & _
           "CREATE TABLE T_2 ( " & _
           "    Name VARCHAR(8) NULL, " & _
           "    Age TINYINT NULL, " & _
           "    Sex BIT NULL, " & _
           "    Department CHAR(16) NOT NULL, " & _
           "    No CHAR(4) NOT NULL, " & _
           "    CONSTRAINT PK_T_2 PRIMARY KEY CLUSTERED (Department, No) " & _
           ");"
oServer.ExecuteImmediate sTransQL, SQLDMOExec_Default

' 6. 啟動 SQL Server 服務 (選用，部署腳本常需確保服務運行)
' Shell "net start MSSQLSERVER", vbHide
' Shell "net start SQLSERVERAGENT", vbHide

物件導向操作替代動態 SQL (更安全、型別檢查)

' 使用 DMO 物件建立資料表 (無需拼接 SQL 字串)
Dim oDB As SQLDMO.Database
Set oDB = oServer.Databases("Db_1")

Dim oTable As New SQLDMO.Table
oTable.Name = "T_3"
oTable.Owner = "dbo"

' 加入欄位
oTable.Columns.Add "ColID", SQLDMOTypeInt, 4
oTable.Columns("ColID").Identity = True
oTable.Columns("ColID").IdentitySeed = 1
oTable.Columns("ColID").IdentityIncrement = 1
oTable.Columns.Add "ColName", SQLDMOTypeNVarChar, 100
oTable.Columns("ColName").Nullable = False

' 加入主鍵
Dim oPK As New SQLDMO.Key
oPK.Name = "PK_T_3"
oPK.Type = SQLDMOKey_PrimaryKey
oPK.Clustered = True
oPK.Columns = "ColID"
oTable.Keys.Add oPK

' 建立資料表
oDB.Tables.Add oTable

服務控制與後續管理

• 服務啟動/停止：`oServer.Service.Start` / `oServer.Service.Stop` (需管理員權限)。
• 修改結構：`ALTER TABLE` 透過 `ExecuteImmediate` 或 DMO 物件屬性修改後 `Alter()`。
• 刪除物件：`oDB.Tables.Remove("T_1")`、`oServer.Databases.Remove("Db_1")`、`oServer.DiskDevices.Remove("Device_1")`。
• 資料存取：DMO **不負責資料查詢/異動**，請使用 ADO / ADO.NET / ODBC / OLE DB。