Get a list of indexes in an Access database

Question

I know that there is a way to get a list of all tables in an Access database using the MsysObjects query:

SELECT MSysObjects.Name FROM MsysObjects WHERE Left$([Name],1)<>'~' AND Left$([Name],4)<>'Msys' AND MSysObjects.Type=1 

Does anyone know a similar (or other) way to get a list of all indexes in an MS-Access database?

Answer 1

You can view TableDef objects to access index names.

 Public Sub ShowIndexNames() Dim tdf As TableDef Dim idx As Index Dim num_indexes As Long On Error GoTo ErrorHandler For Each tdf In CurrentDb.TableDefs num_indexes = tdf.Indexes.Count If Left$(tdf.Name, 4) <> "MSys" Then If num_indexes > 0 Then For Each idx In tdf.Indexes Debug.Print tdf.Name, idx.Name Next idx End If End If Next tdf ExitHere: Exit Sub ErrorHandler: Select Case Err.Number Case 3110 'Could not read definitions; no read definitions ' 'permission for table or query '<Name>'. ' Debug.Print "No read definitions permission for " _ & tdf.Name num_indexes = 0 Resume Next Case Else Debug.Print Err.Number & "-> " & Err.Description GoTo ExitHere End Select End Sub 

Change Revised sub file to ignore MSys * (Access system) tables.

You can also use the ADO OpenSchema method to get index information. The code below lists the index name, linked table, and whether the index is the primary key. I wrote it to use late binding for ADO because it does not require a link for the Microsoft ActiveX Data Objects [version] Library.

 Const adSchemaIndexes As Long = 12 Dim cn As Object ' ADODB.Connection Dim rs As Object ' ADODB.Recordset Dim i As Long Set cn = CurrentProject.Connection Set rs = cn.OpenSchema(adSchemaIndexes) With rs ' enable next three lines to view all the recordset column names ' For i = 0 To (.Fields.Count - 1) ' Debug.Print .Fields(i).Name ' Next i Do While Not .EOF Debug.Print !TABLE_NAME, !INDEX_NAME, !PRIMARY_KEY .MoveNext Loop .Close End With Set rs = Nothing Set cn = Nothing 

If you prefer to consider indexes for a single table rather than for each table in db, pass the table name as the fifth element of the array.

 Set rs = cn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, "tblFoo")) 

Answer 2

If you need to use the .NET OleDb provider and nothing more, you can use

 DataTable indexes = (myOleDbConnection.GetOleDbSchemaTable( System.Data.OleDb.OleDbSchemaGuid.Indexes, new object[] { null, null, null, null, tableName}); 

(specify the correct connection and the right table).

In DataTable indexes, you have indexes and fields for everyone.

Answer 3

Addition to HansUp Answer .

In addition to knowing the names of indexes, it is almost always necessary to know which fields are specified in these indexes. For example, when importing tables from an access database into SQL Server 2008 R2, it does not copy keys and indexes, so I need to restore them. Thus, this script does not properly handle foreign key constraints, but must generate primary keys as well as standard indexes. I will edit it when the FK questions are sorted.

 Public Sub Generate_tSQLIndex() Dim tdf As TableDef Dim idx As Index Dim num_indexes As Long Dim indexdef_tsql As String Dim pk As Boolean On Error GoTo ErrorHandler For Each tdf In CurrentDb.TableDefs num_indexes = tdf.Indexes.Count If Left$(tdf.Name, 4) <> "MSys" Then If num_indexes > 0 Then For Each idx In tdf.Indexes pk = idx.Primary If pk Then indexdef_tsql = "ALTER TABLE [" + tdf.Name + "] WITH CHECK ADD CONSTRAINT [PK_" + tdf.Name + "_" + idx.Name + "] PRIMARY KEY " Else indexdef_tsql = "CREATE " If idx.Unique And Not pk Then indexdef_tsql = indexdef_tsql + "UNIQUE " If idx.Clustered Then indexdef_tsql = indexdef_tsql + "CLUSTERED " Else indexdef_tsql = indexdef_tsql + "NONCLUSTERED " If Not pk Then indexdef_tsql = indexdef_tsql + "INDEX [" + idx.Name + "] ON [" + tdf.Name + "] " indexdef_tsql = indexdef_tsql + "(" For Each fld In idx.Fields indexdef_tsql = indexdef_tsql + "[" + fld.Name + "]" If fld.Attributes = 1 Then indexdef_tsql = indexdef_tsql + " DESC, " Else indexdef_tsql = indexdef_tsql + " ASC, " Next fld If idx.Fields.Count > 0 Then indexdef_tsql = Left(indexdef_tsql, Len(indexdef_tsql) - 2) indexdef_tsql = indexdef_tsql + ")" Debug.Print indexdef_tsql Next idx End If End If Next tdf ExitHere: Exit Sub ErrorHandler: Select Case Err.Number Case 3110 'Could not read definitions; no read definitions ' 'permission for table or query '<Name>'. ' Debug.Print "No read definitions permission for " + tdf.Name num_indexes = 0 Resume Next Case Else Debug.Print Err.Number & "-> " & Err.Description GoTo ExitHere End Select End Sub