維新生活日記

因為是未公開的，所以使用上請小心！

用來抓取所需資料還滿方便的！But when you use, should be very careful..!!

http://www.sqlservercurry.com/2009/04/8-common-uses-of-undocumented-stored.html

8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable

1:47 AM Posted by Suprotim Agarwal
Labels: SQL Server Administration, Transact SQL T-SQL

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 fromhttp://blogs.officezealot.com/mauro/archive/2006/03/12/9402.aspx andhttp://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'