Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

The undocumented sp_MSforeachdb procedure

Late last year, I posted a blog that described the undocumented sp_MSforeachtable stored procedure in Microsoft SQL Server and how it could be used to execute commands on each table within a database. The examples I gave were executing DBCC CHECKTABLE and EXECUTE sp_spaceused() for each table in a given database.

But there's another undocumented stored procedure designed for iteration. This one allows us to iterate through each database in a SQL Server instance. It's called sp_MSforeachdb.

It's usage is very similar to that of sp_MSforeachtable; simply execute the stored procedure, passing it the command that you'd like to execute as a string. For example, to see the space consumed by each database, run the following command.

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

Notice that [?] is used as a placeholder for the heretofore unspecified database name. The results can be seen below.

sp_MSforeachdb1-2008-08-27

As another example, you can execute sp_helpfile on each database by running the following command.

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_helpfile'

 

sp_MSforeachdb2-2008-08-27

Of course undocumented implies unsupported and that there is no guarantees about whether the procedure will exist in future versions of the product. So, use at your own risk and discretion.

Got any favorite uses for sp_MSforeachdb? Feel free to share in the comments below.

Cheers!

Joe

kick it on DotNetKicks.com

Legacy Comments


Peter B
2008-10-01
re: The undocumented sp_MSforeachdb procedure
EXEC sp_MSforeachdb
@command1='use ?; exec sp_changedbowner ''sa''' -- Change all DBs to "sa" owner

EXEC sp_MSforeachdb
@command1='ALTER DATABASE ? SET PAGE_VERIFY CHECKSUM' -- SQL 2005 Best Practice

EXEC sp_MSforeachdb
@command1='?.dbo.sp_change_users_login ''Report''' -- Check for Orphans




Kristina Rotach
2009-12-02
re: The undocumented sp_MSforeachdb procedure
GREAT SCRIPT! I couldn't remember the name of this proc and I needed it to update all stats on server in all databases. I appreciate the free help! :)
PS: found this info via google.

erh
2010-09-02
re: The undocumented sp_MSforeachdb procedure
the first one gives an error if you don't allow system table updates.

Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.


Andrew Jefferson
2011-09-09
re: The undocumented sp_MSforeachdb procedure
Love this sp...

exec model.sys.sp_MSforeachdb
'select @@servername, DB_NAME(), [FileID], [File_Size_MB] = convert(decimal(12,2),round([size]/128.000,2)),
[Space_Used_MB] = convert(decimal(12,2),round(fileproperty([name],''SpaceUsed'')/128.000,2)),
[Free_Space_MB] = convert(decimal(12,2),round(([size]-fileproperty([name],''SpaceUsed''))/128.000,2)) ,
[Name], [FileName], convert(datetime,Getdate(),112) as DateInserted
from dbo.sysfiles'

James
2012-02-16
re: The undocumented sp_MSforeachdb procedure
What if you didn't want to run the script against each database, but only a certain set of dbs?

In my case, I'm working with DBs that have the Version number within the name of the database.

I tried

EXEC sp_MSforeachdb
'IF CHARINDEX(''?'',''v31'')>0 BEGIN
SELECT ''?'' as DBName,Name,Value FROM ?.Preference WHERE Name = ''CorporateName'' OR Name =''Version''
OR Name =''PatchLevel''
END'

It completed successfully, but didn't output any results.

koenig
2012-03-20
re: The undocumented sp_MSforeachdb procedure
@james: you have switched the arguments for the CHARINDEX function. It should read CHARINDEX(''v31'',''?'')...

Amir
2012-05-28
re: The undocumented sp_MSforeachdb procedure
I am experiencing strange result from this command. I used it in an scheduled job which gather all DBs information from several servers and save it in a table. It works fine for 5 servers but for one of them sometimes it is fine and sometimes returns result for part of DBs on the server i.e. if I have 10 DBs it returns result for 3 of them!! and number of DBs are not same so sometimes result is for 3 DBs sometimes 4 and sometimes 10!!

Any Suggestion?

PS: all serevers are exactly the same SQL2005 SP4

Cheers,
Amir

Mario
2012-07-10
re: The undocumented sp_MSforeachdb procedure
Hi,

thanks a lot for this article, it works also in SQL 2012.

:-)

Mario

Dan Fugett
2012-08-19
re: The undocumented sp_MSforeachdb procedure
Any ideas when MS plans to drop MSFOREACH*? Havent they been saying that is the plan for some time now?