Common uses of stored Procedure sp_MSforeachtable

We will get the brief idea about undocumented procedure sp_MSforeachtable of the Master database.

sp_MSforeachtable can be used to loop through all the tables in your databases.

Some of the basic usages of this stored procedures are:-

1.     Display the size of all tables in a database

USE NORTHWIND

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

2.      Display Number of Rows in all Tables in a database

USE ANYDBNAME

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

3.     Rebuild all indexes of all tables in a database

USE ANYDBNAME

GO

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

GO

4.     Disable all constraints of all tables in a database

USE ANYDBNAME

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

5.     Disable all Triggers of all tables in a database

USE ANYDBNAME

EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

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

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

USE ANYDBNAME

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

8.     Update Statistics of all Tables in a database

USE ANYDBNAME

EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'

Please comment if you have any query.