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