SP_MSFOREACHDB Stored Procedure To Iterate Through All Databases In SQL Server

The sp_MSforeachdb is a system stored procedure used to iterate through all databases in SQL Server. There are many places where a DBA would need to run a simple SELECT command across all the databases in a server. It is ideally not possible to just waste time on executing database by database manually. The sp_MSforeachdb can simply save our time at just a click of execution on the current instance.

There are two other ways to iterate through all databases, one by using the CURSOR and the other by using the WHILE LOOP. Further, the CURSOR and WHILE LOOP can help us in implementing some iterative functionality that cannot be achieved using the sp_MSforeachdb.

To check the sp_MSforeachdb code for your reference, please execute the code as mentioned below on any of your databases. Basically, they are internally executed from master-level database.

exec sp_helptext sp_MSforeachdb

General Example of sp_MSforeachdb:

declare @cmd varchar(500) 
set @cmd='your command here' 
exec sp_MSforeachdb @cmd

Example of sp_MSforeachdb to PRINT all databases:

Here, the idea is to just print all the databases that are present on your server for information purposes.

declare @cmd varchar(500) 
set @cmd='print "?"' 
exec sp_MSforeachdb @cmd  

Example of sp_MSforeachdb to SELECT all databases:

The code on this block is just going to select the list of databases. 

declare @cmd varchar(500) 
set @cmd='SELECT "?" ' 
exec sp_MSforeachdb @cmd

Example of sp_MSforeachdb to fetch count of tables from each database:

I am taking an example of selecting the count from sys.tables from each database. Here, I have added an IF condition to exclude the system databases from being iterated.

declare @cmd varchar(500) 
set @cmd='IF "?" not in ("master","model","msdb","tempdb")
BEGIN
USE ?
select DB_Name(),count(1) from sys.tables
END' 
exec sp_MSforeachdb @cmd

Example of sp_MSforeachdb to iterate through databases that are part of Availability Group (Primary Server):

One good example of using the sp_MSforeachdb that just iterates through the databases that are included in the primary server’s availability group. 

declare @cmd varchar(500) 
set @cmd='IF "?"  not in
(SELECT A.database_name       
FROM Sys.dm_hadr_database_replica_cluster_states A       
INNER JOIN Sys.dm_hadr_availability_replica_states B on A.replica_id=b.replica_id 
WHERE ISNULL(b.role,3) =1 
AND b.is_local=1 )
BEGIN
USE ?
select DB_Name(),count(1) from sys.tables
END' 
exec sp_MSforeachdb @cmd  

Examination: USE “?” placeholder is one of the most important key while using the sp_MSforeachdb while placing each database before your command respectively. If the placeholder is missed, the stored procedures are just going to iterate the total number of times the databases we have on the server but on the same database, we are currently on. So always be cautiously while using the placeholders.

• Tweet
• Share
• Share
• Share
• Share