SQL Server provides a large number of built-in stored procedures that can tell us all sorts of useful information about what’s going on in our SQL Server databases. For example, the sp_help* set of system stored procedures allow us to return information regarding the structure of our databases, or any of the objects within that are listed in sys.syobjects, including the system stored procedures themselves. This means that if you’re intrigued to know the current state of the code in any of SQL Server’s system stored procedures, there is an easy way to find out.

A recent, popular article by Scott Swanberg shows a range of T-SQL scripts for exploring your SQL Server Databases, including investigating database object dependencies using the sp_msdependencies system stored procedure. If you’ve a few idle moments and need a laugh, use sp_helptext to take a look at its definition. The code comments are littered with casual, chatty asides, such as “…do not raiserror as we may be calling this blindly and this is not a real error“, and “Just in case, remove self-refs from #t1” or my personal favorite, “Note that triggers are weird“.

It’s as if the developer expected his code to be peer reviewed and ‘fixed’ but the team decided simply to chuck it out there, and then never touched it again. The code was clearly written on SQL Server 2000 or earlier, since it happily references system tables such as sysobjects (owned by dbo), instead of the catalog views, as well as various old Sybase throwbacks such as sysreferences.

Now, admittedly, I’ve picked on an unsupported system stored procedure here and not many others will be in such a state of disrepair, but there’s no doubt that many more of them are long overdue a makeover.

Since we have sp_helptext to allow us to poke around in the source code anyway, and since Microsoft clearly struggles to keep up with improving and updating all of them, why not open source their development? Microsoft could invite members of the community to improve the existing procedures, then vet the most promising, verify their output and incorporate them. That way we might end up with Adam Machanic’s sp_whoisactive as part of the engine instead of the rather less refined sp_who2, or Kimberley Tripp’s vastly superior version of sp_helpindex!

What do you think? Feel free to nominate the system stored procedure that you think most needs fixing, and why.