A hidden feature sp_MSforeachtable–run on entire tables of a Database TIP# 51

Problem:-

Sometimes it happened that you need to run a single statement on entire tables which exists in database.  so most of the time we think of cursor which run for each sys.objects (table)  and we execute the dynamic statement by replacing table name.

Solution:

Although the solution we are thinking is correct there is no issue at all but SQL SERVER made our life more each by providing “sp_MSForEachTable”  which is a hidden stored procedure.

By the name it is clear that it will run on each table.

Lets understand this by an example. Suppose you want to  row count of each table then you can write following statement.

DECLARE @tblRowCount AS TABLE (Counts  INT,
                               TableName VARCHAR(100))

INSERT INTO @tblRowCount (Counts,TableName)
EXEC sp_MSforeachtable
@command1='SELECT COUNT(1) As counts,''?'' as tableName FROM ?'

SELECT * FROM @TblRowCount ORDER BY Counts desc

Now when we run it we will get row count of each table as shown in below snap

 

A part from it you can use this hidden feature in maintenance also like running re indexing, re organizing entire table index etc.

I hope this may help you some where.

Enjoy!!!

RJ!!