HOWTO: Use ADO to Retrieve Table Index Information

ID: Q185979

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2

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: You may need to alter the information in the connection string to connect to your SQL Server 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=sa;pwd=;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

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").

REFERENCES

Microsoft Developer Network, search on: "OpenSchema Method - ADO"

Additional query words:

Keywords : kbADO kbADO150 kbADO200 kbDatabase kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbhowto