April 13, 2009

8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable




In my previous post, I had explained 6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb . In this article, we will see some common uses of the
undocumented procedure sp_MSforeachtable of the Master database

sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure

Display the size of all tables in a database


USE NORTHWIND


EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"




Display Number of Rows in all Tables in a database


USE YOURDBNAME


EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'




Rebuild all indexes of all tables in a database


USE YOURDBNAME


GO


EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"


GO




Note: DBCC DBREINDEX has been deprecated in SQL 2005. Microsoft says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead."

Disable all constraints of all tables in a database


USE YOURDBNAME


EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"




Disable all Triggers of all tables in a database


USE YOURDBNAME


EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'




Delete all data from all tables in your database


-- disable referential integrity


EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'


GO


 


EXEC sp_MSForEachTable '


 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1


  DELETE FROM ?


 else


  TRUNCATE TABLE ?


'


GO


 


-- enable referential integrity again


EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'


GO




To RESEED all table to 0, use this script


EXEC sp_MSForEachTable '


IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1


DBCC CHECKIDENT (''?'', RESEED, 0)


'


GO




The two tips shown above have been taken from http://blogs.officezealot.com/mauro/archive/2006/03/12/9402.aspx and http://www.sqljunkies.com/WebLog/roman/archive/2006/03/08/18620.aspx

Reclaim space from dropped variable-length columns in tables or indexed views


USE YOURDBNAME


EXEC sp_MSforeachtable 'DBCC CLEANTABLE(0,''?'') WITH NO_INFOMSGS; ';




Update Statistics of all Tables in a database


USE YOURDBNAME


EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'



Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

2 Responses to "8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable"
  1. Anonymous said...
    April 14, 2009 at 6:33 AM

    Interesting post - is there a way to use that to get the schema for each table visited?

  2. Bob Sovers said...
    June 4, 2009 at 4:46 PM

    This will give you the full name, the table name, and the schema:


    Exec sp_MSForEachTable '
    Select ''?'' as [Full Name]
    , PARSENAME(''?'',1) as [Table]
    , PARSENAME(''?'',2) as [Schema]
    '

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions