I actually stumbled upon this very stored procedure on the blog post by Suprotim Agarwal of sqlservercurry.com titled: How to skip a Table or Database while using sp_MSforeachdb or sp_MSforeachtable while just doing a little research about SQL Server. I honestly didn’t know this existed. I barely learned about the stored procedure “sp_MSforeachdb” only after attending Brent Ozar’s BLITZ! 60 Minute SQL Server Takeovers presentation during the 24hrs of PASS event. I walked away with valuable information as Brent delivered the content very well. So when I learned of “sp_MSforeachtable” I had to immediately test it.
I have used “sp_spaceused” in the past and of course I constructed a CURSOR that looped through the tables using either INFORMATION_SCHEMA.TABLES or sys.tables. This time around I wanted to achieve the same results without a CURSOR and thankfully “sp_MSforeachtable” with a little creativity allowed me to do so. So here is what I came up with.
CREATE TABLE #tmpTblSpace ( ID INT IDENTITY(1,1) ,name NVARCHAR(75) ,rows INT ,reserved NVARCHAR(50) ,data NVARCHAR(50) ,index_size NVARCHAR(50) ,unsed NVARCHAR(50) ) INSERT INTO #tmpTblSpace EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''' SELECT * FROM #tmpTblSpace DROP TABLE #tmpTblSpace
If I had just ran (Line: 12) alone the results are not quite as appeasing which is precisely why I created the temp table, better formatting.
EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''
As I was practicing database mirroring with SQL Server 2008 I loaded up the sample databases (AdventureWorks) and needed to change the recovery model from Simple to Full. Then it dawned on me… that you can use the sp_MSForEachDB to change the recovery model for all of the adventureworks databases. Here is how it’s done.
EXEC sp_MSforeachdb 'IF (''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')) EXECUTE (''ALTER DATABASE [?] SET RECOVERY FULL'')'