SP_MSFOREACHDB Stored Procedure in SQL Server 2012

Introduction

 

In this article, I describe the Sp_msforeachdb System Stored Procedure in SQL Server 2012. Sp_msforeachdb allows us to execute a T-SQL statement against every database in the current SQL Server instance. In this article, I describe the Sp_msforeachdb Stored Procedure, the use of it and how to make a backup of all the databases using the Sp_msforeachdb Stored Procedure.

 

In my previous article, I described the use of Sp_msforeachtable; you can visit: Sp_msforeachtable in SQL Server 2012

 

Sp_msforeachdb Stored Procedure

 

It is an undocumented Stored Procedure that allows you to iterate through all the databases in a SQL Server instance. sp_MSforeachdb will execute a T-SQL statement against every database associated with the current SQL Server instance.

 

The SP "sp_MSforeachdb" is found in the "master" database and especially useful when you're performing database administration and maintenance tasks, such as backup operations.

 

Syntax

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
set @cmd1 ='your 1st command'
set @cmd2 ='your 2nd command'
set @cmd3 ='your 3rd command'
exec sp_MSforeachdb @command1=@cmd1,
@command2=@cmd2,
@command3=@cmd3

Showing all the databases

declare @cmd varchar(500)
set @cmd='select ''?'''
exec sp_MSforeachdb @cmd

Output

 

 

 

Print the name of all databases

declare @cmd varchar(500)
set @cmd='USE ? PRINT DB_NAME()'
EXECUTE sp_msforeachdb @cmd

Output

 

 

 

Showing every object in each database

declare @cmd varchar(500)
set @cmd='select "?", count(*) as TotalObjects from [?].dbo.sysobjects'
exec sp_MSforeachdb @cmd

Output

 

 

 

Showing the size of each database

declare @cmd varchar(500)
set @cmd='use [?];exec sp_spaceused '
exec sp_MSforeachdb @cmd

Output

 

 

 

Showing the column names in each database

declare @cmd varchar(500)
set @cmd='SELECT name FROM ?.SYS.COLUMNS'
exec SP_MSFOREACHDB @cmd

Output

 

 

 

Showing the number of columns in each database

declare @cmd varchar(500)
set @cmd='select ''?'' as [database name],count(*) as [no of column] from [?].sys.tables'
exec sp_MSforeachdb @cmd

Output

 

 

 

Creating a backup of each database

EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'',''ReportServer'')
BACKUP DATABASE ? TO DISK = ''d:?.bak'''

Output

 

 

 

Summary

In this article, I described the Sp_msforeachdb Stored Procedure in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.