My brain hurts

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”:

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:

Photo by Ashley Batz on Unsplash.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: