SQL-DMO Examples: Indexes
SQL-DMO will be removed in the next version of Microsoft SQL Server. Avoid using SQL-DMO feature in new development work, and plan to modify applications that use SQL-DMO.
This example illustrates using SQL Distributed Management Objects (SQL-DMO) to create a unique, nonclustered index on a Microsoft SQL Server table.
The IndexedColumns property, a write-only property, is used to specify columns participating in a SQL Server index when the index is created. The IndexedColumns property value uses the SQL-DMO multistring data type. Column name identifiers in the string are quoted by using the bracket characters ([]). If more than one column is specified, separate column identifiers using a comma, as in: [OrderID],[ProductID].
' Get the Products table. Note: Create and connect of SQLServer
' object used is not illustrated in this example.
Dim tableProducts As SQLDMO.Table
Set tableProducts = _
oSQLServer.Databases("Northwind").Tables("Products")
' Create a new Index object, then populate the object defining a
' unique, nonclustered index on the indicated filegroup.
Dim idxProductName As New SQLDMO.Index
idxProductName.Name = "idx_Products_ProductName"
idxProductName.FileGroup = "fgNorthwindIdx"
idxProductName.Type = SQLDMOIndex_Unique
idxProductName.IndexedColumns = "[ProductName]"
' Create the index by adding the populated Index object to its
' containing collection.
tableProducts.Indexes.Add idxProductName