retrieve constraint name for a table

[virginie]

retrieve constraint name for a table
hi

i need to know can i in vb or sql, retrieve the name of a constraint for a table.
i need it because i want to delete in table the key, so constraints.

alter table table1 drop constraint constraintname

the problem that i need to retrieve this "constraintname"
i know how to retrieve specific data on table :
FieldInfo(0) = tdSource.Fields(X).Name
FieldInfo(1) = tdSource.Fields(X).Type
FieldInfo(2) = tdSource.Fields(X).Size
FieldInfo(3) = tdSource.Fields(X).Attributes

how can i retrieve constraint name?

 

[VBslammer]

You can determine table constraints using ADOX with code similar to the following:

Public Sub GetIndexInfo(ByVal strTableName As String)
Dim cnn As New ADODB.Connection
Dim rsSchema As ADODB.Recordset
Dim fld As ADODB.Field
Dim rCriteria As Variant

Set cnn = CurrentProject.Connection

'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&quot
rCriteria = Array(Empty, Empty, Empty, Empty, strTableName)

Set rsSchema = cnn.OpenSchema(adSchemaIndexes, rCriteria)

While Not rsSchema.EOF
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
cnn.Close
Set cnn = Nothing
Set fld = Nothing

End Sub


Call the procedure like this (using your table name):

GetIndexInfo("JacketTypes&quot

To return the following information:


TABLE_CATALOG
Null
------------------------------------------------
TABLE_SCHEMA
Null
------------------------------------------------
TABLE_NAME
JacketTypes
------------------------------------------------
INDEX_CATALOG
Null
------------------------------------------------
INDEX_SCHEMA
Null
------------------------------------------------
INDEX_NAME
PrimaryKey
------------------------------------------------
PRIMARY_KEY
True
------------------------------------------------
UNIQUE
True
------------------------------------------------
CLUSTERED
False
------------------------------------------------
TYPE
1
------------------------------------------------
FILL_FACTOR
100
------------------------------------------------
INITIAL_SIZE
4096
------------------------------------------------
NULLS
1
------------------------------------------------
SORT_BOOKMARKS
False
------------------------------------------------
AUTO_UPDATE
True
------------------------------------------------
NULL_COLLATION
4
------------------------------------------------
ORDINAL_POSITION
1
------------------------------------------------
COLUMN_NAME
JacketTypeID
------------------------------------------------
COLUMN_GUID
Null
------------------------------------------------
COLUMN_PROPID
Null
------------------------------------------------
COLLATION
1
------------------------------------------------
CARDINALITY
0
------------------------------------------------
PAGES
1
------------------------------------------------
FILTER_CONDITION
Null
------------------------------------------------
INTEGRATED
True
------------------------------------------------


Remember to set a reference to the ADO library to make this work.

Later
Slammer