ayesamson

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

Results

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

Results

Amended

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