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 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:

sp_msforeachdb-in-sql-server.jpg

Print the name of all databases:


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

Output:

sp_msforeachdb-in-sql-serverr.jpg

Showing every object in each database:


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

Output:

sp_msforeachdb-in-sql-server-.jpg

Showing the size of each database:


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

Output:

sp_msforeachdb-in-sql-serverr-.jpg

Showing the column names in each database:


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

Output:

sp_msforeachdb-in-sql-server-2012.jpg

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:

sp_msforeachdb-in-sql-serverr-2012.jpg

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:

sp_msforeachdb-in-sql-serverr-2012-.jpg

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.