INF: How to Use ListIndexedColumns in SQL-DMO in Visual Basic

• Microsoft SQL Server version 6.5

SUMMARY

This article provides an example, written in Microsoft Visual Basic 5.0, that shows how to use ListIndexedColumns with SQL Distributed Management Objects (SQL-DMO).

MORE INFORMATION

Before beginning, open the Project menu and click References. Make sure you have added a reference to the Microsoft SQLOLE Object Library. Otherwise, if that reference is missing, you must execute Regsvr32.exe against Sqlole65.dll, to put the proper COM entries in the registry.

The example below gets the name of the columns that make up an index on the authors table in the pubs database. ListIndexedColumns returns a Column object, which is used to get the name of that indexed column. And it loops through the indexed column objects until complete.

   Dim ServerObject As New SQLOLE.SQLServer
   ' Ensure the parameters  on next line are passed within 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 

Additional query words: prodsql DMO VB

Keywords : SSrvDMO SSrvProg
Version : WINNT:6.5
Platform : winnt
Issue type : kbhowto kbinfo