Blog Post

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating