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:



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.