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.
  • 3920

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:

sp_MSforeachdb sql.jpg

Show all the database by replacing '?' with '}' character.

declare @cmd varchar(250)

set @cmd='select ''}'''

exec sp_MSforeachdb @cmd,@replacechar='}'

Output:

sp_MSforeachdb with replacechar in sql.jpg


Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.