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
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
Display Number of Rows in all Tables in a database
Rebuild all indexes of all tables in a database
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
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
Disable all Triggers of all tables in a database
Delete all data from all tables in your database
To RESEED all table to 0, use this script
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
Update Statistics of all Tables in a database
Did you like this post?
|subscribe via rss||subscribe via e-mail|
|print this post||follow me on twitter|