sp_MSForEachDB – tricks of the trade

First things first, a few words on Microsoft undocumented procedures (and tools):

it is this simple – USE THEM WITH CAUTION. There are several out there, and they are all great. If you compare sp_who (documented) with sp_who2 (UN-documented) you will see the advantages.

In the case of sp_MSForEachDB (UN-documented), there are several warnings I would like to bring to your attention:
1. Do not abuse it, since it is a global cursor. 
2. UN-documented means that if something goes wrong, you will not be getting support

To the point: sp_MSForEachDB accepts several parameters: @RETURN_VALUE is an integer that is set to the return value associated with SP @command1 is a nvarchar(2000) field for specifying the first command to run against each database @replacechar is a nchar(1) field that represents the character used in the commands you are executing that will be replaced with the database name prior to being executed @command2 is a nvarchar(2000) field for specifying the second command to run against each database @command3 is a nvarchar(2000) field for specifying the third command to run against each database @precommand is a nvarchar(2000) field for specifying a command to be run prior to processing any commands (@command1, @command2, @command3) against any databases @postcommand is a nvarchar(2000) field for specifying a command to be run after all the commands against all databases have been processed.

Example: exec dbo.sp_msForEachDb @command1='SELECT DB_NAME(DB_ID(''?'')) AS DatabaseName' So far all is clear.

Here come the trick questions: How do I get to filter out some of the databases? Does this procedure always have to run for ALL databases?

Answers: Yes – we can filter out some databases. Here is how: For example, we know that the system databases have id from 1 to 4. So, if we were able to run the procedure under a condition of “database id > 4”, then we are set. HOW? exec dbo.sp_msForEachDb @command1='IF DB_ID(''?'') > 4 BEGIN select DB_NAME(DB_ID(''?'')) AS DatabaseName END' Good. And to bring it even further: exec dbo.sp_msForEachDb @command1='IF DB_ID(''?'') in (1,3,5,7) BEGIN select DB_NAME(DB_ID(''?'')) AS DatabaseName END' And so on…

Bottom line: Think about this: BI (Business Intelligence) tools give similar functionality. In SQL Server we have “Maintenance Plans” GUI which is the essential functionality needed to create packages which run towards any set of databases. Furthermore, if you would like to be elaborate, then you would use the BI tools (SSIS in particular) which give endless possibilities for loops, variable mapping, conditional task flows, and so on.