sp_MsForEachDB

sp_MsForEachDB perform an action on all of the databases.

exec sp_MsForEachDB @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand

• @command1 - the command to be executed by sp_MsForEachDB.
• @replacechar - a character in the command string that will be replaced with the database name being processed (default '?').
• @command2 and @command3 are two additional optional commands that can be run for each database (@command2 will be run after @command1, @command3 will be run after @command2).
• @whereand - an additional constraints to help identify the rows in the sysobjects database that will be selected.
• @precommand - a command to be run prior to processing any database.
• @postcommand - a command to be run after all commands have been processed against all databases.

Examples:

declare @cmd1 varchar(500)

declare @cmd2 varchar(500)

declare @cmd3 varchar(500)

set @cmd1 = 'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''

set @cmd2 = 'if ''?'' <> ''tempdb'' backup database ? to disk=''c:\temp\?.bak'''

set @cmd3 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'

exec sp_MSforeachdb @command1=@cmd1,

                    @command2=@cmd2,

                    @command3=@cmd3

GO

EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'