Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database

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

0.00/5 (No votes)
6 Oct 2014CPOL 6.2K  
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:

SQL
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:

sp_msforeachtable_rowcount

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)