Find primary key name by code

[MichaelS]

Hi



I want to drop a primary key by code(C#.VB,tsql)
I do not Know the key name.

How can I do that?

Thanks
Michael

[MichaelS]

those are mdb files.
and I now the table and file name

Michael

[orange]

Michael,
I concocted the following code based on some materials I had. This is vba to get the Primary Key bassed on the Tablename. Perhaps you can adapt as needed, or post back with more details.

'---------------------------------------------------------------------------------------
' Procedure : GetPrimaryKey
' Author : Jack
' Date : 22/09/2011
' Purpose : To get the primary key from a table's index collection
'
'Input: TableName
'Returns: 4 asterisks a blank and The name of the Primary Key if a PK exists
' 4 asterisks a blank and "No Primary Defined" if no PK exists
' 4 asterisks a blank and "Table does not exist" if the table does not exist
'---------------------------------------------------------------------------------------
'
Function GetPrimaryKey(sTableName As String) As String
Dim mDebug As Boolean
' mdebug = True to get the prints
' mdebug = False to bypass the prints
mDebug = True 'False
Dim btblExists As Boolean
btblExists = False
Dim tdf As DAO.TableDef
Dim db As DAO.Database
On Error GoTo GetPrimaryKey_Error

Set db = CurrentDb
For Each tdf In db.TableDefs
With tdf
If tdf.name = sTableName Then
btblExists = True
GetPrimaryKey = "**** No Primary Defined"
For Each idxLoop In .Indexes
If mDebug Then Debug.Print "table: " & .name
With idxLoop
If mDebug Then Debug.Print " " & "Index: " & .name


' Enumerate Properties collection of each
' Index object.
If mDebug Then Debug.Print " Properties"
For Each prpLoop In .Properties
If mDebug Then Debug.Print " " & prpLoop.name & _
" = " & IIf(prpLoop = "", "[empty]", _
prpLoop)
' Is this a Primary Key
If prpLoop.name = "Primary" Then
If prpLoop = True Then
hldPrimary = "Y"
GetPrimaryKey = "**** " & idxLoop.name
Else
hldPrimary = "N"
End If
Else
End If
' Is this a unique index
If prpLoop.name = "Unique" Then
If prpLoop = True Then
hldUnique = "Y"
Else
hldUnique = "N"
End If
Else
End If

' Does this index Ignore NULLS?
If prpLoop.name = "IgnoreNulls" Then
If prpLoop = True Then
hldIgnoreNulls = "Y"
Else
hldIgnoreNulls = "N"
End If
Else
End If

Next prpLoop

' Enumerate Fields collection of each Index
' object.
i = 0
If mDebug Then Debug.Print " *Fields* making up this index-> " & idxLoop.name
For Each fldLoop In .Fields
i = i + 1
If mDebug Then Debug.Print " " & "Field: " & fldLoop.name

' if the index is composed of multiple fields
'then repeat the table, index information for each field

If i > 1 Then
Else
End If

Next fldLoop

End With
Next idxLoop
Else
End If
End With
Next tdf
If btblExists = False Then GetPrimaryKey = "**** Table does not exist"

On Error GoTo 0
Exit Function

GetPrimaryKey_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure GetPrimaryKey of Module DataDictionary"

End Function

Routine to test the function

Sub testGetPrimaryKey()
Debug.Print GetPrimaryKey("Myks")
End Sub