Microsoft KB Archive/185979

= HOWTO: Use ADO OpenSchema to Retrieve Table Index Information =

Article ID: 185979

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q185979



SUMMARY
If you need to retrieve index information for a table using ActiveX Data Objects (ADO) you must use a custom stored procedure or the ADO OpenSchema method. The ADO OpenSchema method simplifies access to the indexes of a table and provides a universal way to retrieve that information. The following sample code shows how to retrieve index information for a specific table using the OpenSchema method.



MORE INFORMATION
To see how this method works, follow these steps:  Start a new Visual Basic standard EXE project. Form1 is created by default. Set a reference to the Microsoft Activex Data Objects Library in the project.  Paste the code in the form-load event of Form1. Note the following:  You may need to alter the information in the connection string to connect to your SQL Server database. The user,, must have permissions to perform these operations on the database.

Dim cn As ADODB.Connection Dim rsSchema As ADODB.Recordset Dim fld As ADODB.Field Dim rCriteria As Variant

Set cn = New ADODB.Connection

With cn  .Provider = "MSDASQL"   'default Provider=MSDASQL .CursorLocation = adUseServer .ConnectionString = "driver={SQL Server};server=(local);" & _ "uid=UserName;pwd=StrongPassword;database=pubs" .Open End With

'Pass in the table name to retrieve index info. The other 'array parameters may be defined as follows: '   TABLE_CATALOG  (first parameter) '   TABLE_SCHEMA   (second) '   INDEX_NAME     (third) '   TYPE           (fourth) '   TABLE_NAME     (fifth, e.g. "employee") rCriteria = Array(Empty, Empty, Empty, Empty, "employee")

Set rsSchema = cn.OpenSchema(adSchemaIndexes, rCriteria)

Debug.Print "Recordcount: " & rsSchema.RecordCount

While Not rsSchema.EOF Debug.Print "==================================================="

For Each fld In rsSchema.Fields Debug.Print fld.Name Debug.Print fld.Value Debug.Print "" Next rsSchema.MoveNext Wend

rsSchema.Close Set rsSchema = Nothing cn.Close Set cn = Nothing Set fld = Nothing

</ol>

The first row contains information about the table and the following rows contain index information for each field participating in an index. Therefore, the number of rows returned is equal to the number of indexed fields + 1.

You could also reference the index in the preceding rsSchema example with fld("INDEX_NAME") and the column name with fld("COLUMN_NAME").

<div class="references_section">