How to Use ListIndexedColumns in SQL-DMO by Using Visual Basic (194522)

MORE INFORMATION

Before you start, you must make sure that you have added a reference to the correct library. To add a reference, follow these steps:

1. From the Project menu, click References.
2. Select the appropriate reference for the version of SQL Server that you are using:
   

   SQL Server 2000

   For SQL Server 2000, you must add a reference to the Microsoft SQL-DMO Object (Sqldmo.dll). By default, Sqldmo.dll is located in the following folder:
   
   C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SQLDMO.dll
   
   

   SQL Server 7.0

   For SQL Server 7.0, you must add a reference to the Microsoft SQL-DMO Object Library (Sqldmo.dll).
   
   

   SQL Server 6.5

   For SQL Server 6.5, you must add a reference to the Microsoft SQLOLE Object Library (Sqlole65.dll).
   
   

Note: If the appropriate reference is missing, you must run Regsvr32.exe against either the Sqldmo.dll or the Sqlole65.dll file to put the correct COM entries in the registry.

The following sample code gets the name of the columns that make up an index on the authors table in the pubs database. ListIndexedColumns returns a Column object that is used to get the name of that indexed column. The sample loops through the indexed column objects until complete.

Note: This sample is written for SQL Server 6.5. For SQL Server 7.0 or SQL Server 2000, replace all instances of "SQLOLE" with "SQLDMO".

   Dim ServerObject As New SQLOLE.SQLServer
   ' Ensure the parameters  on next line are passed in double quotation
   ' marks
   ServerObject.Connect <servername>, <LoginID>, <Password>
   Dim indexcolname As String
   Dim idxcnt As Integer
   Dim idxloops As Integer
   Dim idxObj As SQLOLE.Index
   Dim idxcol As SQLOLE.Column
   idxcnt = 0
   idxcnt = ServerObject.Databases("pubs").Tables("authors").Indexes(1). _
      ListIndexedColumns.count
   While idxcnt > 1
      Set idxcol = ServerObject.Databases("pubs").Tables("authors"). _
         Indexes(1).ListIndexedColumns(1)
      indexcolname = idxcol.Name
      MsgBox indexcolname
      idxloops = 1
      While idxloops < idxcnt
         idxloops = idxloops + 1
         Set idxcol = ServerObject.Databases("pubs").Tables("authors"). _
            Indexes(1).ListIndexedColumns(idxloops)
         indexcolname = idxcol.Name
         MsgBox indexcolname
      Wend
   idxcnt = 0
   Wend