THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Please keep the system stored procedures updated

One of the things I like about SQL Server (and Sybase for that matter) is that it comes with tons of system stored procedures. Who doesn't find procs like sp_who, sp_help, and and sp_helprotect indispenable?

But unfortunately it seems to me that Microsoft is on the path to abandon these system stored procedures. Judging from the fact that many of these procs are not being updated to keep up with the new versions of SQL Server and the responses from Microsoft to query the system catalogs and DMVs to make up the difference when this is pointed out and reported, I'd say that updating these system stored procedures are at least not on their priority list.

True, one can argue that you could write scripts to query system catalogs and DMVs yourself, and Microsoft has provided quite a few scripts through their blogs and codeplex projects. But that is not the same as shipping these procs in the product and therefore making them ubiquitous. Yes, you probably can't expose everything--that the new system catalogs and DMVs have to offer--through these system stored procedures. I'd be happy if Microsoft can stick to the 80/20 rules to have the system stored procedures cover 80% of the common admin tasks and leave the other 20% for people to write their own scripts, or at least just keep the existing system procedures updated, whereever applicable.

It just seems to me to be such a huge collective waste of time for the users to keep writing the same or similar admin scripts when they could be written once by Microsoft.

Published Thursday, May 07, 2009 5:46 PM by Linchi Shea
Filed under:



TiborKaraszi said:

I absolutely agree. As a consultant, I'm very annoyed by this. I'm at a customer site, and the sp's that comes with the product doesn't reflect the current version of the product. It isn't always practical to install your own version, for instance you probably want to mark it as a system proc, which isn't supported - is the client OK with that? In the end, this all is a huge waste of time.

The problem here, I must assume is with backwards compatibility. I mean, how much effort is it to include included column in sp_helpindex or separate schema from owner in sp_help?

I think I have "the solution":

Document the system procs, that only return data, as "ad hoc". Meaning that the inteface can change with next version. I.e., these are only meant to be used ad hoc. As soon as you want to consume the result from code (which is where backwards compatibility comes to play) then use catalog views instead. If you write code, then spending a few minutes to query catalog views doesn't really matter.

Another alternative would be to never modify the old system proc, but add sp_helpindex105, and then sp_helpindex110. That way, we can remember the name of the proc and keep backward compatibility. But downside is for MS is large number of code to test and support.

May 8, 2009 3:49 AM

Linchi Shea said:


I'd be happy with either approach.

Another compromise perhaps is to add new info to a new resultset only. Since many of these system stored procedures return multiple resultsets already, adding another resultset to a proc should lessen the burden of updating the code that consumes the proc. For ad hoc use, it may not be perfect to have info that should be right next to each other scattered in the output. But as long as, for instance, sp_helpdb gives me all the info about that database (including database mirroring etc), it is still much better than leaving people wondering what may be missing.

May 8, 2009 9:00 AM

noeldr said:

This, in fact, is very annoying and inconsistent on their part.

For example:

RESTORE FILELISTONLY and RESTORE HEADERONLY have been following the "upgrade" trend by adding columns to the previously existing results. (2000-2005-2008)

Why not do that consistently?

May 8, 2009 9:18 AM

Linchi Shea said:

RESTORE FILELISTONLY and RESTORE HEADERONLY are considered as T-SQL language proper, and as such they must be kept up to date with the version. I guess sp_helpdb and the like are considered utilities, and perhaps not quite up there in the pecking order.

May 8, 2009 9:40 AM

Ranga Narasimhan said:

Good point Linchi.

As a side note, sp_helpindex displays disabled indexes. MS should add a new column to show if a index is enabled or disabled.

May 8, 2009 2:55 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement