Using sp_MSforeachtable

I actually stumbled upon this very stored procedure on the blog post by Suprotim Agarwal of 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.

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

One thought on “Using sp_MSforeachtable

  1. Glad you liked the presentation! These stored procs come in so handy when you want to script some actions quickly against lots of tables. Really helpful.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s