The hidden Stored Procedures in SQL-II

Just like sp_MSforeachtable  you also have a stored procedure which runs for each DB.

It is sp_MSforeachdb

SYNTAX

EXEC @RETURN_VALUE=sp_MSforeachdb @command1,

     @replacechar,

     @command2,

     @command3,

     @precommand,

     @postcommand

where:

@RETURN_VALUE - the return value which will be set by "sp_MSforeachtable"

@command1 - first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)

@replacechar - 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 for each table, where @command2 runs after @command1, and @command3 will be run after @command2

@precommand - nvarchar(2000) parameter and specifies a command to be run prior to processing any table

@postcommand - nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

Some example usages-

• To check space used by each DB

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

• Change all DB owners to sa

EXEC sp_MSforeachdb @command1='use ?; exec sp_changedbowner ''sa'''