Traversing through every database: sp_MSforeachdb

This is undocumented stored procedure created by Microsoft.

The SP "sp_MSforeachdb" is found in the "master" database. This SP is used to execute a single or multipel T-SQL statements against every database defined to a SQL Server instance.

Here is the syntax for calling this undocumented SP:

EXEC @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar,
@command2, @command3, @precommand, @postcommand

Where:
@RETURN_VALUE - is the return value which will be set by "sp_MSforeachdb"
@command1 - is the first command to be executed by "sp_MSforeachdb" and is defined as nvarchar(2000)
@replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
@command2 and @command3 are two additional commands that can be run against each database
@precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any database
@postcommand - is also an nvarchar(2000) field used to identify a command to be run after all commands have been processed against all databases.

Example:
Lets say, we have to run "DBCC CHECKDB" command for each database onto server. Run the following code:



DECLARE @cmd1 varchar(500)
DECLARE @cmd2 varchar(500)
SET @cmd1 =
'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
SET @cmd2 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'
EXEC sp_MSforeachdb @command1=@cmd1,
@command2=@cmd2