A Hidden Feature sp_MSforeachtable–Run on Entire Tables of a Database - TIP# 51

Problem

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

Solution

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

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

Let's understand this by an example. Suppose you want row count of each table, then you can write the 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 the below snapshot:

Apart 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 somewhere.

Enjoy!!!

Filed under: CodeProject, DENALI, Sql server, SQL SERVER 2008 - R2, SQL SERVER 2014, TIPS
Tagged: hidden feature of sql server, rowcount, sp_msforeachtable, system command