HOWTO: Use ADO OpenSchema to Retrieve Table Index Information (185979)

MORE INFORMATION

To see how this method works, follow these steps:

1. Start a new Visual Basic standard EXE project. Form1 is created by default.
2. Set a reference to the Microsoft Activex Data Objects Library in the project.
3. 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, Username, 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
   
   					

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