The Undocumented Sp_MSforeachdb Procedure in SQL Server

Introduction

All versions of SQL Server have some Stored Procedures or functions that are not documented in the MSDN. This may be because they are used by Microsoft internally. This type of Stored Procedure or function (undocumented) can be changed by Microsoft without any notification.

The "sp_MSforeachdb" Stored Procedure comes with SQL Server, but it is not documented in MSDN. This Stored Procedure could be found in the Master database. The Stored Procedure "sp_MSforeachdb" is very similar to sp_MSforeachtable. This Stored Procedure is used to execute a TSQL statement against each database of a SQL Server instance.

Syntax

sp_MSforeachdb [ @command1 = ] 'command1' [ , [ @replacechar = ] replacechar ] [ , [ @command2 = ] command2 ] [ , [ @command3 = ] command3 ] [ , [ @precommand = ] precommand] [ , [ @postcommand = ] postcommand]

Parameter

Parameter	Description
@command1	It is the first command to be executed by this Stored Procedure and it data type is nvarchar(2000)
@replacechar	It is a character in the command string that needs to be replaced with the table name being processed. Default value of this parameter is a "?".
@command2 @command3	@command2 and @command3 are two additional commands that can be run for each table. Here first Command1 is executing then command2 and then command3 will execute.
@precommand	This is command that to be run before the processing any table. It data type is nvarchar(2000)
@postcommand	This is command that to be run after the processed all table. It data type is nvarchar(2000)

Definition of procedure in SQL Server 2008 R2

CREATE PROCEDURE sys.sp_MSforeachdb
@command1 NVARCHAR(2000),
@replacechar nchar(1) = N'?',
@command2 NVARCHAR(2000) = NULL,
@command3 NVARCHAR(2000) = NULL,
@precommand NVARCHAR(2000) = NULL,
@postcommand NVARCHAR(2000) = NULL
AS
    SET deadlock_priority low

 /* This proc RETURNs one or more rows for each accessible db, with each db defaulting to its own result SET */
 /* @precommand and @postcommand may be used to force a single result SET via a temp table. */

 /* Preprocessor won't replace within quotes so have to use STR(). */
 DECLARE @inaccessible NVARCHAR(12), @invalidlogin NVARCHAR(12), @dbinaccessible NVARCHAR(12)
 SELECT @inaccessible = LTRIM(STR(CONVERT(INT, 0x03e0), 11))
 SELECT @invalidlogin = LTRIM(STR(CONVERT(INT, 0x40000000), 11))
 SELECT @dbinaccessible = N'0x80000000'  /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in CONVERT() */

 IF (@precommand IS NOT NULL)
  EXEC(@precommand)

 DECLARE @origdb NVARCHAR(128)
 SELECT @origdb = DB_NAME()

 /* IF it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
   /* CREATE the SELECT */
 EXEC(N'DECLARE hCForEachDatabASe cursor global for SELECT name from mASter.dbo.sysdatabASes d ' +
   N' where (d.status & ' + @inaccessible + N' = 0)' +
   N' and (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (hAS_dbaccess(d.name) = 1))' )

 DECLARE @retval INT
 SELECT @retval = @@error
 IF (@retval = 0)
  EXEC @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1

 IF (@retval = 0 and @postcommand IS NOT NULL)
  EXEC(@postcommand)

   DECLARE @tempdb NVARCHAR(258)
   SELECT @tempdb = REPLACE(@origdb, N']', N']]')
   EXEC (N'use ' + N'[' + @tempdb + N']')

 RETURN @retval

Example

The following script helps to list a database in a SQL Server instance.

Example script:

exec sp_MSforeachdb 'print "?"'

Output:



In another example, the following script helps us to determine the space used and allocated for each database in the SQL Server instance.

Example script:

exec sp_MSforeachdb 'EXECUTE sp_spaceused [?];'

Output:



Common uses of this Stored Procedure

This stored produce may be used for the following purposes:

• To get the size of all the database of a SQL Server instance
• To change the owner all the databases to the specific user
• To check the Logical and Physical integrity of all objects in the database
• To get all the physical names and attributes (like size, growth, usage) of all databases in a SQL Server instance
• Run the same command on each database without cursors (like takes back up of all database in SQL Server instance)

Conclusion

These undocumented Stored Procedures can be used if we want to perform the same operation on each database of a SQL Server instance. Please note that Microsoft may change the functionality and definition of this Stored Procedure at any time.