Please keep the system stored procedures updated

re: Please keep the system stored procedures updated

TiborKaraszi — Fri, 08 May 2009 07:49:08 GMT

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.

re: Please keep the system stored procedures updated

Linchi Shea — Fri, 08 May 2009 13:00:59 GMT

Tibor;

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.

re: Please keep the system stored procedures updated

noeldr — Fri, 08 May 2009 13:18:44 GMT

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?

re: Please keep the system stored procedures updated

Linchi Shea — Fri, 08 May 2009 13:40:15 GMT

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.

re: Please keep the system stored procedures updated

Ranga Narasimhan — Fri, 08 May 2009 18:55:35 GMT

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.