The un-documented stored procedure sp_msforeachdb found in the Microsoft SQL Server database msdb has been documented on the internet by developers for years, and it has grown in popularity by those who have found it.
It is extremely useful in that you can define a query and then have it executed on every database installed on an instance of SQL Server. I have found the utility of this stored procedure to save myself a lot of time over the years. Sure, you could roll your own iterator with a cursor walking across the system catalog of databases. But this procedure saves a lot of keystrokes compared to using a cursor and saves time when you need it. If you’re interested in finding out more about sp_msforeachdb you can do a simple google search and get plenty of help for how the syntax works.
One thing that is common in most of the blogs and other unofficial documentation of this stored procedure is the warning that it could be deprecated at any time, and that it isn’t supported by Microsoft. I’m wondering two things:
- Because it has grown so popular for many years, why doesn’t Microsoft simply support it?
- Why not write your own?
Only Microsoft can answer the first question, and maybe there are good reasons not to include it in the tool.
Regarding the second question, if you really need to use the functionality of an unsupported routine in your processes then you should probably not use it; at least not use it directly. As I stated above, the capabilities of sp_msforeachdb can be replicated by writing your own cursor and walking the database catalog.
So, if you require the functionality of sp_msforeachdb then here is my suggestion. Because cursor safety net exists, why not write your own stored procedure. If you’re in a hurry today then just call sp_msforeachdb. Then if Microsoft takes it away you can implement a cursor technique in your stored procedure and your system continues to run smoothly. If they don’t take it away, you haven’t wasted any time duplicating the capability.
There are a number of utility procedures and functions in SQL Server that have the same caveat that they are not supported. Using this wrapper procedure technique could be a good way to safely use the existing features that may be deprecated and could be replicated in other ways, allowing us to use these features with a rapid path towards future proofing and not wasting time if it isn’t needed.
What’s your take on using un-documented tools in commercial software? Leave your comments online or drop an email to btaylor@sswug.org.
Cheers,
Ben