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
I am Rajat Jaiswal from India. I am working as a Technology specialist in one of the reputed company in India with 12+ years of experience. I am a Microsoft Data Platform MVP & Alibaba Cloud MVP.
I have always had an affinity to learn something new in technology. Database, Web development, Cloud computing are the areas of my interests always.
I have been a regular contributor to technologies with more than 300+ tips. Learning & Sharing is one of my aims always. Writing blogs, delivering sessions, helping on forums is my hobby.