Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

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

Print | posted on Wednesday, August 27, 2008 5:28 PM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# 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



10/1/2008 1:30 AM | Peter B
Gravatar

# 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.
12/2/2009 6:42 AM | Kristina Rotach
Gravatar

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

9/2/2010 3:17 PM | erh
Gravatar

# re: The undocumented sp_MSforeachdb procedure

Notice that [?] is used as a placeholder for the heretofore unspecified database name. The results can be seen below.
10/16/2010 4:00 PM | columbia jackets
Gravatar

# re: The undocumented sp_MSforeachdb procedure

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.

snow boots | snow boots for women | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup
10/19/2010 4:34 AM | furry boots
Gravatar

# re: The undocumented sp_MSforeachdb procedure

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free video converter for Mac, This video converter for Mac is more stable in converting video files and
10/26/2010 1:28 AM | hanly
Gravatar

# re: The undocumented sp_MSforeachdb procedure

Undocumented Stored Procedure - sp_MSForeachdb by VTS

Visit us @ http://visiontechno.net/studymats/?cat=21 for details.
MS Sql Server provides you with some undocumented stored procedures -
sp_MSforeachtable - allows you to process some code against each and every table in a single database.
This is a stored procedure which comes with SQL server by default. Its present in the master database.
This is used to process a single command or multiple commands against tables in sinlge database.
Suppose, I am building a table where we would be having a series of records.
Each record would contain two columns - one column would contain the name of the table present
in the database and other column would contain the count of the rows in that particular table.
What we would do in that case is:
Now here is acode that produces similar results using the undocumented SP "sp_MSforeachtable":
Below is the syntax for calling the sp_MSforeachtable Stored procedure:
exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand
Where:
@RETURN_VALUE - the return value which will be set by "sp_MSforeachtable"
@command1 - first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)
@replacechar - character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
@command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
@whereand - can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
@precommand - nvarchar(2000) parameter and specifies a command to be run prior to processing any table
@postcommand - nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

1/17/2011 8:16 AM | akhil
Gravatar

# re: The undocumented sp_MSforeachdb procedure

Nice, I'm currently testing something for our SQL database on the handy shop site. So far I had some issues, but I think that this post on the MSforeachdb procedure gave me some insights on how to make it better.

Thanks
5/24/2011 11:52 AM | Daniel
Gravatar

# 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'
9/9/2011 3:24 AM | Andrew Jefferson
Gravatar

# 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.
2/16/2012 9:19 AM | James
Gravatar

# re: The undocumented sp_MSforeachdb procedure

@james: you have switched the arguments for the CHARINDEX function. It should read CHARINDEX(''v31'',''?'')...
3/20/2012 6:21 AM | koenig
Gravatar

# 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
5/28/2012 9:18 PM | Amir
Gravatar

# re: The undocumented sp_MSforeachdb procedure

Hi,

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

:-)

Mario
7/10/2012 10:55 AM | Mario
Gravatar

# 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?
8/19/2012 12:40 PM | Dan Fugett
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET