Retrieve list of indexes in an Access database
I know there's a way to get a list of all tables in an Access database by using the quering the MsysObjects:
SELECT MSysObjects.Name FROM MsysObjects
WHERE
Left$([Name],1)<>'~' AND
Left$([Name],4)<>'Msys' AND
M开发者_如何学PythonSysObjects.Type=1
Does anybody know a similar (or other) way to retrieve a list of all indexes in an MS-Access database?
You can examine TableDef
objects to access the 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
Edit: Revised the sub to ignore MSys* (Access system) tables.
You could also use ADO's OpenSchema
method to retrieve information about indexes. The code below lists the index name, associated table, and whether the index is the primary key. I wrote it to use late binding for ADO because that doesn't require setting the reference for 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 examine indexes for a single table rather than for every table in the db, pass the table name as the fifth element of an array.
Set rs = cn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, "tblFoo"))
If you need to use .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});
(specifing the right connection and the right table).
In the indexes DataTable you have indexes and fields all toghether.
Augmentation to HansUp's answer.
In addition to knowing the names of indexes it's almost always essential to know which fields are referenced in those indexes. For example, when importing tables from an access DB to SQL Server 2008 R2, it doesn't copy keys and indices, so I need to regenerate them. Thusfar this script doesn't properly handle foreign key constraints, but should generate primary keys as well as standard indexes. I will edit it when the FK issues are sorted.
Public Sub Generate_tSQLIndex()
Dim tdf As TableDef
Dim fld As DAO.Field
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
精彩评论