Trying to get table indexes information in SQL Server 2012 I identified a strange situation for one scenarion.
I have a table that contains two indexes referenced to some fields: Field_1 and Field_3 mapped over int, null columns (the number means the existing field order into the table designed few years ago...).
I am trying to get information about these indexes like this:
nRetCode = ::SQLStatistics(hstmtAux, NULL, 0, NULL, 0, (TCHAR*)(LPCTSTR)strTempTable, SQL_NTS, SQL_INDEX_ALL, SQL_ENSURE);
if (nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO)
{
nRetCode = ::SQLBindCol(hstmtAux, 4, SQL_C_SHORT, &swNonUnique, sizeof(SWORD), &cbNonUnique);
nRetCode = ::SQLBindCol(hstmtAux, 5, SQL_CHAR, szIdxQualif, sizeof(CHAR) * 130, &cbIdxQualif);
nRetCode = ::SQLBindCol(hstmtAux, 6, SQL_C_CHAR, szIdxName, sizeof(CHAR) * 130, &cbIdxName);
nRetCode = ::SQLBindCol(hstmtAux, 7, SQL_C_SHORT, &swType, sizeof(SWORD), &cbType);
nRetCode = ::SQLBindCol(hstmtAux, 8, SQL_C_SHORT, &swSeqInIdx, sizeof(SWORD), &cbSeqInIdx);
nRetCode = ::SQLBindCol(hstmtAux, 9, SQL_C_CHAR, szIdxColName, sizeof(CHAR) * 130, &cbIdxColName);
while (bNoFetch || (nRetCode = ::SQLExtendedFetch(hstmtAux, SQL_FETCH_NEXT, 1, &crow, &rgfRowStatus)) == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO)
{
if (cbIdxName != SQL_NULL_DATA && _tcslen((TCHAR)szIdxName) > 0)
{
// rest of the code
}
// the rest of the code
Becasuse SQLExtendedFetch() is deprecated I used SQLFetchScroll() but the behavior is the same from my interest point of view.
Usally, I get the right information about indexes but in one situation I encounte a strange behavior. It's about having a clustered index into a scenario.
When Field_1 is Non-Unique, Non-Clustered and Field_3 is
Clusted index I get the right information.
But if the index Field_1 is Clustered and the Field_3 is
Non-Unique, Non-Clustered I get no information about Field_1 index (eg. szIdxName and szIdxColName are "" and their length is -1 that means SQL_NULL_DATA).
So, I have no Index information. Within while loop, with the next iteration I get correct information about the second index Field_3.
I'm not sure whether the problem is with SQLStatistics, the bindings or SQLFetchScroll (they all always return SQL_SUCCESS). It looks like a problem with the driver when the first index is clustered.
According to
SQLStatistics documentation if my swType parameter is
SQL_TABLE_STAT I have no information for index or column. For this scenarios I have no indexes of combined columns.
For the good scenario I observed that my while loop had 3 iterations including one of having swType = SQL_TABLE_STAT without information in szIdxName. But for the bad scenario the loop had only 2 iteration.
Any ideas for fixing this problem or alternative ways for retrieving indexes information?
