“Just because you can, does NOT mean you should.” This is one of my favorite sayings that I like to apply in many aspects of my life, including when I am dealing with SQL Server.
For example, there are a lot of undocumented stored procedures that can be used and are very handy in certain situations (i.e. sp_MSforeachdb, sp_who2, xp_fixeddrives, xp_readerrorlog, etc.) (http://social.technet.microsoft.com/wiki/contents/articles/16975.sql-server-undocumented-stored-procedures.aspx)
However, I would NEVER suggest using undocumented stored procedures in production code, because they can be deprecated or modified by Microsoft without any notice, which will certainly cause problems.
So, what should you do instead of using one of these undocumented stored procedures?
Build your own version of the stored procedure that mimic the functionality desired. Here are a few ideas on how this can be done:
- In order to find out what an undocumented stored procedures is doing, run sp_helptext (http://technet.microsoft.com/en-us/library/ms176112.aspx). Please be advised, this will NOT give you the code running for extended stored procedures (i.e. xp_*), it will only list the dll name. However, this outputted code can be used to create your own version of the stored procedure.
- Additionally, PowerShell can be used to mimic the functionality of many of the undocumented extended stored procedures. In a previous post, I showed how PowerShell could be used instead of xp_fixeddrives (https://sqlbadboy.wordpress.com/2012/10/19/storage-space-check-it-and-plan-for-growth/).
- Use and/or modify someone else’s code according to their licensing agreement.
- Sp_whoisactive (http://sqlblog.com/tags/Who+is+Active/default.aspx) – This is a widely used stored procedure that can be used as a replacement for sp_who and sp_who2.
- Sp_foreachdb (http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/) – This stored procedure could be tailored to fit your needs, instead of relying on sp_MSforeachdb, which has been known to miss databases.
So, although you could use undocumented stored procedures in many situations, you definitely should NOT use them in production code.