sp_MSforeachdb Stored Procedure in SQL Server 2012
In this article I describe Sp_msforeachdb System Stored procedure in SQL Server .This SP is used to execute T-SQL statement against every database defined to a SQL Server instance.
Introduction
In my previous article I described about sp_MSforeachtable Stored Procedure in SQL Server 2012. Now in this article I am going to explain about sp_MSforeachdb system stored procedure. Several times in our project we needed to process through all the database on a single SQL Server instance. It is an undocumented stored procedure which is not listed in MSDN books.
sp_MSforeachdb Stored Procedure
This stored procedure allows you to iterate through all database to perform a specific task or run a query against all the database in a SQL Server instance. It This stored procedure is found in "master" database. For example: If you want to see all the names of database stored in SQL Server or you want to see the space used by each database. It becomes very tedious to perform any operation on each database one by one, so this is the best way to run a query against each database.
Syntax
exec sp_MSforeachdb @command1,
@replacechar,
@command2,
@command3,
@precommand,
@postcommand
|
where,
@command1- specifies first SQL query to be executed.
@replacechar- specifies character in the SQL query that will be replaced with the database name being processed. Default value is '?'.
@command2 and @command3- specifies additional commands.
@precommand- specifies a command that will be run prior to processing any database.
@postcommand- specifies command used to be run after all command have been executed against all the database.
Example
Show all the database:
declare @cmd varchar(250)
set @cmd='select ''?'''
exec sp_MSforeachdb @cmd
Output:
Show all the database by replacing '?' with '}' character.
declare @cmd varchar(250)
set @cmd='select ''}'''
exec sp_MSforeachdb @cmd,@replacechar='}'
Output: