How to get indexed column name of table from SQL by VB6.0 OR 2005 with ADODB connection? (DBMR2040)
The SQL cannot get indexed column name directly, it should parse multi-SQL to get. First, get num_column, reserve3 from system.sysindex, second, from above result then match in column_name of system.syscolumn.we can write a store procedure by ec to realize the above process.
We can get database metadata of indexed column by the ADO function of openSchema via SQLStatistics.
It should be noticed that OleDB of DBMaker5.1 can support this method. For DBMaker4.3 version, you should modify the Provider value to “MSDASQL” and data source value to “dbsample4”;
Usage as following:
DBMaker4.x
Dim conStr As String
Set conn = New ADODB.Connection
conStr = "Provider=MSDASQL; data source=dbsample4; user id=sysadm; password=; "
conn.Open conStr
'Set rs = conn.OpenSchema(adSchemaIndexes, Array(Empty, "scheme_name", Empty, Empty, "table_name"))
While Not rs.EOF
Debug.Print rs!COLUMN_NAME
rs.MoveNext
Wend
DBMaker5.x
Dim conStr As String
Set conn = New ADODB.Connection
conStr = "Provider=dmole51; data source=dbsample5; user id=sysadm; password=; "
conn.Open conStr
Set rs = conn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, "CARD"))
While Not rs.EOF
Debug.Print rs!COLUMN_NAME
rs.MoveNext
Wend
Version: DBMaker 5.x, DBMaker 4.3
Product: Normal
Platform: all windows