Common uses of SQL SERVER Undocumented Stored Procedure sp_MSforeachtable

I have recently faced completed some data migration tasks for a project. During different problems I have found some uses of sp_MSforeachtable store procedure to make migration steps easy. I have found following problems

Problems:

1. During data migration I have faced “Constraint” Problem. Like Foreign Key or Unique Key

2. I have multiple triggers that fires on data insertion and I need to shut down all before migration

3. I need to know space used for each table and there are more than 1000 tables in database

4. During 2^nd Cycle for migration I need to delete data from all existing tables and then reinsert again and this will increase code and time to insert delete statement before each table

and lot of others as well. Then I find one solution for all my problems and am sp_MSforeachtable procedure. This is an undocumented procedure in master database. sp_MSforeachtable can be used to loop through all the tables in your database. I have used following as my solutions. There are some other uses of this procedures and  mentioned below

1. Disable all constraints of all tables in your database

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

After migration enable aa constraints again

EXEC sp_MSforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL"

2. Disable/Enable all Triggers on all tables in your database

EXEC sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"

EXEC sp_MSforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL"

3. Space Used of all tables in your database

EXEC sp_MSforeachtable "EXEC sp_spaceused ?"

4. Return Number of Rows for each table

EXEC sp_MSforeachtable "Select ''?'',count(*) as TotalRows from ?"

5. Rebuild all indexes for all tables

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

Use alter index instead of reindex in future versions of sql server.

6. Delete data from all tables of your database

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

EXEC sp_MSforeachtable "DELETE FROM ?"

EXEC sp_MSforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL"

7. Re claim identity for all columns

EXEC sp_MSforeachtable "

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

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

You need to replace schema name before each table to execute this query

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

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

9- Update statistics

EXEC sp_MSforeachtable "Update Statistics ? WITH ALL"

10- Generating Insert statement for all tables

EXEC sp_MSforeachtable "exec up_generate_insert ?"

Where sp_generate_insert is a user created procedure that return insert statements for parameter value

I have gathered information from my experience and other blogs like

http://bytes.com/topic/sql-server/answers/492005-alter-table-nocheck-constraint-still-some-dependencies

and

suprotim agarwals blog: for common uses of the undocumented Stored Procedure