Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

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

Legacy Comments


Fanshawe Student.
2009-10-06
re: The undocumented sp_MSforeachtable procedure
Thanks, needed this for my advanced database class!

Dan Sutton
2010-04-19
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


anime
2010-10-30
re: The undocumented sp_MSforeachtable procedure
Interesting post - Is there a way to use that to get the schema for each table accessed?

High PR Text Links
2010-12-06
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.

BrAun CoRporatIon
2011-02-21
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!

Gary Forbis
2011-06-17
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.

ambrosi philistino
2011-07-12
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

Sell Comic Books
2011-09-06
re: The undocumented sp_MSforeachtable procedure
Your blog is informative and brilliant. Keep it up!

How To Get Rid Of Cat Urine Smel
2011-09-13
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.

Stop Balding Now
2011-09-14
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.

Larry SS.
2012-02-27
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.