Drop & Recreate Indexes in SMO

Category: sql server smodmo


Price Brattin on Thu, 18 Jul 2013 20:03:53

The objective is to change a column dataType to VarChar(30) instead of Char(30) and the code is below. We have to drop the indexes first because the column is used in some of the indexes for that table. The objective is to save a copy of the indexes, make change to the column, and use the copy of the indexes to rebuild them on the table. The problem is that the Tbl.Indexes(IdxCtr).Drop() method call drops the indexes in the copy too. Then at the the end when the code tries to rebuild the indexes on the live table, the copies of the indexes contain nothing. What can be done to make the indexes not drop in the copy when the code drops them on the live data?

Dim SMOConn As ServerConnection Dim SMOServer As Microsoft.SqlServer.Management.Smo.Server Dim TmpName As String Dim Ctr, IdxCtr As Integer Dim Tbl As Table Dim IdxColl As Microsoft.SqlServer.Management.Smo.IndexCollection
Dim Idx As Microsoft.SqlServer.Management.Smo.Index Try DispMsg = "New ServerConnection" SMOConn = New ServerConnection(ADOdotNetConn) DispMsg = "New SMOServer" SMOServer = New Microsoft.SqlServer.Management.Smo.Server(SMOConn) SMOServer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql Dim DBName As String = SMOConn.DatabaseName Dim DB As Database = SMOServer.Databases(DBName) Tbl = DB.Tables("Account") IdxColl = Tbl.Indexes For IdxCtr = Tbl.Indexes.Count - 1 To 0 Step -1 Tbl.Indexes(IdxCtr).Drop() Next Dim Col As Column = Tbl.Columns("User1") Col.DataType = DataType.VarChar(30) Tbl.Columns.Refresh() Tbl.Alter() '----This does not work For Each Idx In IdxColl Tbl.Indexes.Add(Idx) Next Tbl.Indexes.Refresh() Tbl.Alter() DB.Tables.Refresh() ShowStatus("Finished Processing") Catch ex As Exception frmErrorInfo.txtErrMsg.Text = DispMsg frmErrorInfo.ShowDialog() End Try End Sub

Price Brattin, SQLServer & SharePoint 2010 MCP, Microsoft Dynamics SL Consultant


Allen Li - MSFT on Mon, 22 Jul 2013 07:24:15

Hi Price,

First, we can get the script definition script with Script() function. After dropping the index, we can execute the script to generate the index again. I made a simple example, you can refer to it:

Dim srv As Server
        srv = New Server()

        ' Reference the AdventureWorks2012 database. 
        Dim db As Database
        db = srv.Databases("AdventureWorks2012")

        ' Declare a Table object and reference the HumanResources table. 
        Dim tb As Table
        tb = db.Tables("Employee", "HumanResources")

        ' There is an index named "TestIndex" on HumanResources. Employee table which was created by me
        Dim idx As Index
        idx = tb.Indexes("TestIndex")

        Dim IndexStr As System.Collections.Specialized.StringCollection

        IndexStr = idx.Script()
        For Each item As String In IndexStr


        For Each item As String In IndexStr
For more detail information, you can refer to the following link:

Index.Script Method

If you have any feedback on our support, please click here.