Undocumented stored procedures in MS SQL, why?

Question

I came to know about some uses of undocumented stored procedures (such as 'sp_MSforeachdb' ...etc) in MS SQL.

What are they? and Why they are 'undocumented'?

Answer 1

My speculation would be because they are used internally and not supported, and might change. sp_who2 is another that I find very handy. Maybe management studio activity monitor uses that one - same output. Did I mention undocumented probably means unsupported? Don't depend on these to stick around or produce the same results next year.

Answer 2

+1 on precipitous. These procs are generally used by replication or the management tools, they are undocumented as the dev team reserves the right to change them any time. Many have changed over the years, especially in SQL 2000 Sp3 and SQL 2005

Answer 3

sp_MSforeachdb and sp_MSforeachtable are unlikely to change.

Both can be used like this:

EXEC sp_MSforeachtable "print '?'; DBCC DBREINDEX ('?')"

where the question mark '?' is replaced by the tablename (or DB name in the other sp).

Answer 4

Undocumented means unsupported and that MS reserves the right to change or remove said commands at any time without any notice whatsoever.

Any documented features go through two deprecation stages before they are removed. An undocumented command can be removed, even in a service pack or hotfix, without any warning or any announcements.

Answer 5

It is most likely that one of the internal SQl Server developers needed these stored procedures to implement the functionality that they were working on, so they developed it and used it in their code. When working with the technical documentation people they covered the scope of their project, and included in the official documentation only the portion of their project that applied to the customer. Over time, people found the extra stored procedures (because you can't hide them) and started using them. While the internal SQl Server developers wouldn't want to change these undocumented procedures, I'm sure they would in two seconds if they had to to for their next project.

Answer 6

As others have said, they are unsupported features that are not intended for general consumption, although they can't stop you from having a go, and indeed, sometimes they can be very useful.

But as internal code, they might have unexpected side-effects or limitations, and may well be here one day and gone the next.

Use them carefully if you wish, but don't rely on them entirely.