Silviu-Marius Ardelean's blog

a software engineer's web log

Getting Table’s indexes experiences – workaround

Trying to get table indexes information in SQL Server 2012 I identified a strange situation within a specific method that I was using so long but it was not acting as expected in one situation.

The way of getting indexes information using the ODBC C API into that old and inherited method looks like:

Usually, I got the right information about indexes but in one situation I encounter a strange behavior. It’s about having a clustered index into a scenario.

I have a table that contains two indexes referenced to some fields: IndexField_1 and IndexField_3 mapped over int, NULL fields. When IndexField_1 is Non-Unique, Non-Clustered and IndexField_3 is Clustered index I get the right information.
But if the index IndexField_1 is Clustered and the IndexField_3 is Non-Unique, Non-Clustered I get no information about IndexField_1 index (eg. szIdxName and szIdxColName are “” and their length is -1 that means SQL_NULL_DATA). Within while loop, with the next iteration I get correct information about the second index IndexField_3.

Because SQLExtendedFetch() is deprecated I tried using SQLFetchScroll() but the behavior is the same from my interest point of view.

I was not sure whether the problem is with SQLStatistics, the bindings or SQLFetchScroll (they all always return SQL_SUCCESS). It looks such 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 field. But for this scenario I had no indexes of combined fields.
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. So it looks like SQLExtendedFetch() is not getting the last one index.

After some googling and research without very significant solutions I decided to apply a workaround by avoiding the old API and I rewrite my method.

So, in order to get table indexes information, I have chosen a direct SQL query into SYS tables: sys.tables, sys.indexes, sys.schema.

Because I preferred getting also information about the index’s composed fields, I applied a second additional SQL query:

and I have collected data into a container of defined structure according to my SQL Indexes interest information:

The last member vectColumns stores information about the columns that are used for a specific index.

Finally, the new method that collects table indexes information looks like:

In this way I have complete information about the indexes of my tables.

Conclusion: When the C/C++ API doesn’t give you any hopes don’t forget that SQL saves you.

Silviu Ardelean

Software Engineer

More Posts - Website

Follow Me:


2 Responses to “Getting Table’s indexes experiences – workaround”

  1. Dan

    Good fix, but just a comment: isn’t it improper usage of TCHAR into the initial code?

  2. You’re right. I inherited that old code from ex-colleagues that I never met, but even correcting that detail it does not solve the main issue. Having a project base on multi-byte character set, the _tcslen() became strlen() in background (strlen.asm).
    Also, because strTempTable is a CString a cast to (SQLCHAR*)(LPCTSTR) does not change something in the idea of getting all the table’s indexes for my bad scenario.

Leave a Reply