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.
Posted
9 Oct 2009 11:56 AM
by
Feodor Georgiev