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