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_MSforeachtable procedure

As database administrators or developers, sometimes we need to perform an action on all of the tables within a database. For example, we may want to disable check constraints for all tables prior to loading a bunch of data; or perhaps we need to run DBCC CHECKTABLE for every table in our database to ensure the integrity of all pages and structures that make up the tables.

We could, of course, create a script that uses a cursor to loop through all of the tables in the database to do our bidding through dynamic SQL. Yuck!

Fortunately, there's a better way. It's the undocumented sp_MSforeachtable stored procedure in the master database. It's like a cursor in that it loops through each table in the current database and executes a script that you define. But it requires considerably less code.

For example, the following script checks the integrity of each table in the AdventureWorks database using the DBCC CHECKTABLE command. Notice that a [?] is used as a placeholder for the table name in the SQL statement.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])';

That's a whole lot more palatable than developing a cursor-based solution!

Here's another example. The following script reports the space used and allocated for every table in the database.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';

So, the next time you need to loop through each table, give the sp_MSforeachtable procedure a try.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Tuesday, October 23, 2007 11:21 AM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# re: The undocumented sp_MSforeachtable procedure

Thanks, needed this for my advanced database class!
10/6/2009 9:51 PM | Fanshawe Student.
Gravatar

# re: The undocumented sp_MSforeachtable procedure

Nice. I took this to its logical extreme: here's something to reindex every table in every database, then truncate the logs and shrink the database files; hope this proves useful to someone:

begin
declare @dbname varchar(50)
declare @logname varchar(50)
declare @sql varchar(2000)
declare cDB cursor for select name from sys.databases
open cDB
fetch next from cDB into @dbname
while @@fetch_status=0
begin
exec ('use ['+@dbname+']')
exec ('sp_MSForEachTable @command1="dbcc dbreindex (''?'')"')
begin try
exec ('alter database ['+@dbname+'] set recovery simple')
declare cLog cursor for select name from sys.database_files where type_desc='LOG'
open cLog
fetch next from cLog into @logname
while @@fetch_status=0
begin
begin try
dbcc shrinkfile (@logname,0)
end try begin catch end catch
fetch next from cLog into @logname
end
close cLog
deallocate cLog
dbcc shrinkdatabase (@dbname, TRUNCATEONLY)
exec ('alter database ['+@dbname+'] set recovery full')
end try begin catch end catch
fetch next from cDB into @dbname
end
close cDB
deallocate cDB
end

4/19/2010 1:33 PM | Dan Sutton
Gravatar

# re: The undocumented sp_MSforeachtable procedure

For example, the following script checks the integrity of each table in the AdventureWorks database using the DBCC CHECKTABLE command. Notice that a [?] is used as a placeholder for the table name in the SQL statement.

10/19/2010 4:39 AM | furry boots
Gravatar

# re: The undocumented sp_MSforeachtable procedure

It's like a cursor in that it loops through each table in the current database and executes a script that you define. But it requires considerably less code.

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:40 AM | fur boots
Gravatar

# re: The undocumented sp_MSforeachtable 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 vide under simple video editing function embedded, support Even Thought This video converter for mac is a shareware, it's unregistered version have very little limitation only a samll water mark in the converted videos.
10/26/2010 1:44 AM | hanly
Gravatar

# re: The undocumented sp_MSforeachtable procedure

Interesting post - Is there a way to use that to get the schema for each table accessed?
10/30/2010 2:24 PM | anime
Gravatar

# re: The undocumented sp_MSforeachtable procedure


Great post! I'm just starting out in community management/marketing media and trying to learn

how to do it well - resources like this article are incredibly helpful. As our company is based

in the US, it's all a bit new to us.
12/6/2010 7:07 AM | High PR Text Links
Gravatar

# re: The undocumented sp_MSforeachtable procedure

Shop Staples® for office supplies, printer ink, toner, copy paper, technology, electronics & office furniture. Get free delivery on all orders over $50.Staples strives to make it easy for our customers, associates and the Find Staples locations Powered by MapQuest. staples Online shopping for Baseball Batting Helmets from a great selection of Sports & Outdoors & more at everyday low prices.Schutt Sports is your source for football helmets, baseball and softball Schutt® Sports offers over 900 custom colors for our softball batter's helmets, baseball helmet Shop Emporio Armani Watches and Jewelry for Exceptional Quality, Design & Elegance. Distinctive Casual, Dress and Fashion Watches for Men & Women.Armani watch is elegant subtle and simplicity,the Italian fashion style.It has significance beyond its own and become a successful career and a symbol of Armani Watches Sep 12, 2006 haaaaa im sturring the kids drinks with the tampoons Added to queueRoy D. Mercer Imitation-Tampoonsby cattomjak173917 views · 5:31 Apr 24, 2008 I am going to Jamaica this summer and happens to fall on the week of Yes, tampons are definitely what you want to use when going swimming. tampoons Abt Electronics has an extensive selection of Gas Cooktops with great features including sealed burners, electronic pilotless ignition, porcelain drip bowls Cooktops from GE Appliances. GE high quality cooktops are available in gas cooktops and electric cooktops with multiple installation options. gas cooktop
12/24/2010 11:22 PM | nrerr
Gravatar

# re: The undocumented sp_MSforeachtable 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/18/2011 2:15 AM | akhil
Gravatar

# re: The undocumented sp_MSforeachtable procedure

It also helps to structure your databases correctly to ensure faster data pulls. This is absolutely crucial when changing multiple databases with the same action. Thanks for the procedure, my system is faster and more solid because of it!
2/21/2011 10:05 PM | BrAun CoRporatIon
Gravatar

# re: The undocumented sp_MSforeachtable procedure

Nice SQL Statement - I currently have to rewrite the database of our jagdbedarf site. This tipps will become useful for this. Thanks
4/10/2011 3:54 AM | Jacob
Gravatar

# re: The undocumented sp_MSforeachtable procedure


Great post! I'm just starting out in community management/marketing media and trying to learn

how to do it well - poparticles en articlescatresources like this article are incredibly helpful. As our company is based

in the US, it's all a bit new to us.
4/16/2011 3:14 AM | ghgf
Gravatar

# re: The undocumented sp_MSforeachtable procedure

I happened across this in an email from a fellow employee. I then searched MSDN and came across the sp_msForEachDb procedure in this example:

EXEC sp_msForEachDb
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'') BEGIN PRINT ''#'';
EXEC #.dbo.sp_msForEachTable ''UPDATE STATISTICS ? WITH FULLSCAN'', @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Stats Updated'''''' END',
@replaceChar = '#'


I verified that @replaceChar can be used with both sp_msForEachDb and sp_msForEachTable. I'm not sure I understand the
@command1 and @command2 part yet. It looks like these procedures can have multiple commands with an implied
GO for each command. I wonder if there is a limit for these parameters or if comma separated parameters not identified are assumed to be commands.
6/17/2011 11:02 AM | Gary Forbis
Gravatar

# re: The undocumented sp_MSforeachtable procedure

Nice website with good information and I am pleased to have read some of them on your blog. Some were good interesting reads and others were alright but could be a little better
7/12/2011 2:26 AM | ambrosi philistino
Gravatar

# re: The undocumented sp_MSforeachtable procedure

Your blog is informative and brilliant. Keep it up!
9/6/2011 2:30 AM | Sell Comic Books
Gravatar

# re: The undocumented sp_MSforeachtable procedure

I liked how the thoughts and the insights of this article is well put together
and well-written. Hope to see more of this soon.
9/13/2011 9:53 PM | How To Get Rid Of Cat Urine Smel
Gravatar

# Moyamoya Disease

The article is wonderfully written and the way
the points were sent across is very understandable. I loved it.
9/14/2011 2:39 AM | Ridgemaegan
Gravatar

# re: The undocumented sp_MSforeachtable procedure

I liked how the thoughts and the insights of this article is well put together
and well-written. Hope to see more of this soon.
9/14/2011 3:22 AM | Stop Balding Now
Gravatar

# A real thanks

Hey, amid all the spam and everything, wanted to put out a real thanks. I was looking for the information you explained here about this procedure after finding a reference to it on another site.

Why is it that stuff like this is undocumented? It seems like one of those basic things that potential DBA's should be aware of. Just another case of 'MS Documentation Style' I guess.
2/27/2012 9:35 AM | Larry SS.
Gravatar

# Dance Music Chart

It's useful! Thanks a lot for sharing this post with us.
Fantastic website
3/28/2012 3:06 AM | Dance Music Chart
Gravatar

# MRE For Sale

I wanted to thank you for this great blog! I really enjoying every little bit of it and I have you bookmarked to check out new stuff you post.
4/4/2012 2:03 AM | MRE For Sale
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET