How to Primary Key and Indexes using ADO in VB6 2

[cmlimnet]

Dear All,

I wanna to get primark key and indexes from table in
Ms SQL 7.0 using ADO in VB. I can get the primary and indexes name using below code but I cant get the column name for primary and indexes.

Set cnn1 = New ADODB.Connection
strCnn = "Provider=SQLOLEDB.1;Password=root;
Persist Security Info=True;
User ID=sqladmin;
Initial Catalog=Northwind;Data Source=BIZSNA;"
cnn1.open strCnn

Set rstSchema = cnn1.OpenSchema(adSchemaIndexes)
Do Until rstSchema.EOF
Debug.Print rstSchema!TABLE_CATALOG
Debug.Print rstSchema!TABLE_SCHEMA
Debug.Print rstSchema!INDEX_NAME
Debug.Print rstSchema!Type
Debug.Print rstSchema!TABLE_NAME
rstSchema.MoveNext
Loop
rstSchema.Close
cnn1.Close
How to get column name for primary key and indexes?
How to differentiate whether it is primary key or indexes?
Thank you very much.

Best Regard,
cmlimnet

 

[SemperFiDownUnda]

You can get alot of information from calling the system stored procedure

sp_help [tablename]

so something like this may help you

dim rs as ADODB.Recordset
set rs = new adodb.recordset

rs.open "sp_help Customers"

this will return multiple recordsets to the recordset object with all the schema information for the given table.
Access the other recordsets by using

rs.NextRecordSet

 

[cmlimnet]

Dear SemperFiDownUnda (Instructor),
Your idea seen help me, but I have no idea of how to access and manipulate these recordset. For example, properties and method of these recordset. Can I get any reference from MSDN?

Thank a lot.

Best Regards,
cmlimnet

 

[SemperFiDownUnda]

if you did this

DIM RS as ADODB.Recordset

Set RS = New ADODB.Recordset
'conn is a ADODB.Connection object created elsewhere

RS.Open "sp_help Customers", conn

'RS has a 1 record recordset with the following info
'rs.fields("Name").Value is the Name of the table
'rs.fields("Owner").Value is who owns the table
'rs.fields("Type").Value is if it is a User or System table
'rs.fields("Created_DateTime").Value is when the table was created

rs.NextRecordset
'RS now has n record recordset 1 row for every column giving Column_name, Type, Computed, Length, Prec, Scale, Nullable, TrimTrailingBlanks,  FixedLenNullInSource, Collation                                                                                                               rs.NextRecordset
'RS now has info about Indentity column

...

'keep issueing rs.NextRecordSet until you get a recordset i with fields of (index_name, index_description, and index_keys) or (constraint_type, constraint_name, delete_action, update_action, status_enabled, status_for_replication, and constraint_keys)

both recordsets have similar information [i]Note - this may vary from different versions of SQL[/i]

Lookup [b]sp_help[/b] is SQL Books online
Lookup [b]recordset.Nextrecordset[/b] on MSDN

 

[cmlimnet]

Dear SemperFiDownUnda (Instructor),
Thank you for your guidance. It is really help me a lot.
The sp_help is only available in MS SQL server, right?
Can we do in such a way that we can get all database schema using ADO without calling specific function from a particular DBMS.

Best Regards,
cmlimnet

 

[cmlimnet]

Dear All,
This is a sample code to get primary key using ADO without calling specific function from a particular DBMS.

dim rstSchema as ADODB.Recordset
dim cnn as ADODB.Connection
.
.
''Get the primary key from all tables
Set rstSchema = cnn.OpenSchema(adSchemaPrimaryKeys)
If Not rstSchema.EOF Then

''Get the primary key from a table specified by TableName variable
rstSchema.Filter = "TABLE_NAME='" & TableName & "'"
While Not rstSchema.EOF
debug.print rstSchema.fields("COLUMN_NAME&quot
rstSchema.MoveNext
Wend
End If
rstSchema.Close


Best Regards,
cmlimnet