SP_MSFOREACHDB Stored Procedure in SQL Server 2012

In this article I describe the Sp_msforeachdb System 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
  1. declare @cmd1 varchar(500)  
  2. declare @cmd2 varchar(500)  
  3. declare @cmd3 varchar(500)  
  4. set @cmd1 ='your 1st command'   
  5. set @cmd2 ='your 2nd command'  
  6. set @cmd3 ='your 3rd command'  
  7. exec sp_MSforeachdb @command1=@cmd1,   
  8. @command2=@cmd2,  
  9. @command3=@cmd3  

Showing all the databases

  1. declare @cmd varchar(500)  
  2. set @cmd='select ''?'''  
  3. exec sp_MSforeachdb @cmd  
Output
 
sp_msforeachdb in sql server 
 

Print the name of all databases

  1. declare @cmd varchar(500)  
  2. set @cmd='USE ? PRINT DB_NAME()'  
  3. EXECUTE sp_msforeachdb @cmd  
Output
 
sp_msforeachdb in sql server 
 

Showing every object in each database

  1. declare @cmd varchar(500)  
  2. set @cmd='select "?", count(*) as TotalObjects from [?].dbo.sysobjects'  
  3. exec sp_MSforeachdb @cmd  
Output
 
sp_msforeachdb in sql server 
 

Showing the size of each database

  1. declare @cmd varchar(500)  
  2. set @cmd='use [?];exec sp_spaceused '  
  3. exec sp_MSforeachdb @cmd  
Output
 
sp_msforeachdb in sql server 
 

Showing the column names in each database

  1. declare @cmd varchar(500)  
  2. set @cmd='SELECT name FROM ?.SYS.COLUMNS'  
  3. exec SP_MSFOREACHDB @cmd   
Output
 
sp_msforeachdb in sql server 
 

Showing the number of columns in each database

  1. declare @cmd varchar(500)  
  2. set @cmd='select ''?'' as [database name],count(*) as [no of column] from [?].sys.tables'  
  3. exec sp_MSforeachdb @cmd   
Output
 
sp_msforeachdb in sql server 
 

Creating a backup of each database

  1. EXECUTE sp_msforeachdb 'USE ?  
  2. IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'',''ReportServer'')  
  3. BACKUP DATABASE ? TO DISK = ''d:?.bak'''  
Output
 
sp_msforeachdb in sql server 
 

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.