It's hard to mention catalog functions without bubbling in fury. Their design is contemptible, their effect is pernicious, they are a confusion and a disgrace. That being said, they are also frequently useful and support for catalog functions is far better than support for selections from standard SQL's INFORMATION_SCHEMA tables.
There is one vital datum that you won't find in any INFORMATION_SCHEMA table and therefore can't determine with any standard SQL:1999 function. We refer to the information provided by the ODBC function SQLSpecialColumns . Any general program needs to know the optimal set of columns that can be used to identify a row in a table uniquely, and knowing the primary key is not necessarily enough. Of course, one should be able to access a table via primary-key data. The trouble is that there may be faster alternatives, such as the ROWID or some other unique key. When the information from SQLSpecialColumns is available, you can do a data change like this:
The essential element in this plan is that there must be a quick unique identifier, and that's the information SQLSpecialColumns can provide. (See Chapter 15, "Locks," for a more complete example with an analogous technique.)
The Bottom Line: Catalog Functions
Use ODBC's SQLSpecialColumns function to find the optimal set of columns that can be used to identify a row in a table uniquely. Use those columns in search conditions to improve the performance of data-change statements.