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
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. |