Microsoft KB Archive/194522

= How to Use ListIndexedColumns in SQL-DMO by Using Visual Basic =

Article ID: 194522

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q194522



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



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:

 From the Project menu, click References.  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, ,  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: kbhowto kbinfo KB194522

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.