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.

SP_MSFOREACHDB Stored Procedure in SQL Server 2012

Reactions

3 Comments

Introduction

Sp_msforeachdb Stored Procedure

Showing all the databases

Print the name of all databases

Showing every object in each database

Showing the size of each database

Showing the column names in each database

Showing the number of columns in each database

Creating a backup of each database

Summary