Have Geek Brain

Undocumented stored procedures in MS SQL, why?

I learned about some undocumented stored procedures (like "sp_MSforeachdb" ... etc) in MS SQL.

What is it? and why are they "undocumented"?

+1


a source to share


6 answers


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



+2


a source


My guess would be because they are used internally and are not supported and are subject to change. sp_who2 is another one that I find very convenient. Perhaps the control studio activity monitor is using the same output. Did I mention that undocumented probably means unsupported? Don't depend on them to stick with or produce the same results in the next year.



+1


a source


sp_MSforeachdb

and is sp_MSforeachtable

unlikely to change.

Both can be used like this:

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

where is the question mark '?' is replaced by a table name (or a DB name in another sp).

+1


a source


Undocumented tools are not supported and this MS reserves the right to change or remove the specified commands at any time without any notice.

Any documented functionality goes through two stages of deprecation before being removed. An undocumented command can be removed even in a service pack or hotfix without any warning or announcement.

+1


a source


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

+1


a source


As others have said, they are unsupported features that are not intended for general consumption, although they cannot stop you from needing to, and indeed, they can be very useful at times.

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

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

0


a source






More articles:


All Articles