Monday, May 9, 2016

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.

No comments:

Post a Comment