When we fire any query in SSMS, it returns data, we generally check status bar/message tab to get the information of the records affected or retrieved.

Do you know SQL Profiler provides facility to capture the record count (ROWCOUNT)?

Though it is small feature and often unnoticed, I would like to describe here. This feature is available since SQL Server 2005.

Sample Database used for this post:

I am using "AdventureWorks2008R2" database - sample database provided by Microsoft SQL Server 2008 R2.

You can download this database from: http://msftdbprodsamples.codeplex.com/

Generally what I do to check affected/retrieved records:

SELECT sp.[StateProvinceID], 
       sp.[StateProvinceCode], 
       sp.[IsOnlyStateProvinceFlag], 
       sp.[Name] AS [StateProvinceName], 
       sp.[TerritoryID], 
       cr.[CountryRegionCode], 
       cr.[Name] AS [CountryRegionName] 
FROM [Person].[StateProvince] sp 
LEFT OUTER JOIN [Person].[CountryRegion] cr 
ON sp.[CountryRegionCode] = cr.[CountryRegionCode];

Query returns 181 rows and status bar displays number of rows retrieved with execution time of the query as shown in the image below:

When we execute query batch (more than one query), stored procedure etc., its difficult to retrieve the record counts of each individual queries if SET NOCOUNT is ON. Here we can take help of SQL Profiler.

Get record count using SQL Profiler:

Start the profiler by selecting following Events:

(For this post, I have selected more events & arranged columns. You can select the events as per your need but needs to include RowCounts column.)

Now, executing the above mentioned same query in SSMS once again and check profiler result, Column RowCounts has the information which I want to know as below:

T-SQL query batch & retrieving affected records:

Now, I am going to execute following T-SQL query batch in SSMS query editor:

SET NOCOUNT ON
BEGIN
DECLARE @countryRegionCode NVARCHAR(10)
DECLARE @personStateCountryData TABLE 
       ( [StateProvinceID] INT, 
         [StateProvinceCode] NVARCHAR(4), 
         [IsOnlyStateProvinceFlag] BIT, 
         [StateProvinceName] NVARCHAR(50), 
         [TerritoryID] INT, 
         [CountryRegionCode] NVARCHAR(4), 
         [CountryRegionName] NVARCHAR(50) ) 

SET @countryRegionCode = 'US'
INSERT INTO @personStateCountryData 
      ( StateProvinceID, 
        StateProvinceCode, 
        IsOnlyStateProvinceFlag, 
        StateProvinceName, 
        TerritoryID, 
        CountryRegionCode, 
        CountryRegionName )
SELECT sp.[StateProvinceID], 
       sp.[StateProvinceCode], 
       sp.[IsOnlyStateProvinceFlag],
       sp.[Name] AS [StateProvinceName],
       sp.[TerritoryID],
       cr.[CountryRegionCode],
       cr.[Name] AS [CountryRegionName] 
FROM [Person].[StateProvince] sp 
LEFT OUTER JOIN [Person].[CountryRegion] cr 
            ON sp.[CountryRegionCode] = cr.[CountryRegionCode] ;

SELECT p.StateProvinceCode,
       p.CountryRegionCode,
       p.CountryRegionName
FROM @personStateCountryData AS p 
WHERE p.CountryRegionCode = 'US' ;
END
GO

SSMS query editor status bar displays 53 rows as expected. But I am not able to get the information of each individual query’s record counts.

That I can see using SQL profiler as below:

We can use filter on RowCounts column same as other columns.

Available filters are: Equals, Not equal to, Greater than or equal, Less than or equal

Hope this is helpful. Thanks.