Randolph Kimberly Tripp has a very neat series of stored procedures on her blog which are rewrites of sp_helpindex.
Unfortunately for those of us with customers still using SQL Server 2000, these scripts are useless, because they are based on DMVs and catalog views built into SQL Server 2005 and higher.
With her permission, I have begun porting the scripts to SQL Server 2000. My biggest challenge so far has been trying to identify whether a column has a unique constraint that is not the primary key. I spent over an hour going through the various system tables (and Google), and came across this piece of code, courtesy of a user on dbForums called “fadace”:
SELECT object_name(id), name
FROM sysindexes
WHERE status & 2 = 2
Combining this into an EXISTS clause has solved my problem, but this is just one example of why our jobs on SQL Server 2000 are more complex to solve, given the advances in SQL Server 2005 and higher.
For reference, my new code looks like this:
SELECT @nonclus_uniq = CAST(status & 2 AS bit)
FROM sysindexes
WHERE id = @object_id AND indid = @index_id