Avoiding Direct System Table Access

Can I write a script that checks for the existence of an index on a particular table?

I recently answered a question like this one on the Microsoft SQL Server public newsgroup at msnews.microsoft.com, and I'd like to share my answer here for two reasons. First, I want to show a convenient way to solve the problem. Second, and perhaps more important, I want to explain why referencing system tables directly can be a bad idea.

Here's a simple query that shows how you can determine whether an index called CustomerID exists on the Northwind database's Orders table:

SELECT INDEXPROPERTY ( object_id('northwind..orders') , 'CustomerID' , 'IndexId' )

This query returns the IndexId of the index if the index exists and returns NULL if the index doesn't exist. The INDEXPROPERTY() function belongs to the family of functions called metadata functions. For more information about these functions, see the "Meta Data Functions" section of SQL Server Books Online (BOL). You'll find several helpful functions that let you query a variety of system-related data.

The newsgroup thread I responded to included the script that Listing 1 shows as a possible solution to the problem. This script would find the information you're looking for, but it requires you to directly query the sysindexes system table. I admit that I regularly query system tables, but it's a nasty habit that I'm trying to break. Microsoft has been warning us for years that we shouldn't query system tables because they're undocumented and could change in future releases. Lately, the company has been mumbling about making good on that promise in the next release of SQL Server, code-named Yukon. I'm trying to rid my queries of direct references to system tables by using system procedures, system functions, and other documented techniques for accessing system data.