http://www.sqlservercentral.com/blogs/sqlandme/2011/12/28/sql-server-row-count-for-all-views-tables/

Printed 2014/04/24 02:37PM

SQL Server – Row count for all views / tables

2011/12/28

Getting row count for all tables in a database is straight forward. You can display row count for all tables by joining sys.objects and sys.partitions as below:

[UPDATE: sys.partitions only shows an approximation of the number of rows. (http://msdn.microsoft.com/en-us/library/ms175012.aspx)%5D

USE   [AdventureWorks2008R2]

GO

 

SELECT      SCHEMA_NAME(A.schema_id) + '.' +

            A.Name, SUM(B.rows) AS 'RowCount'

FROM        sys.objects A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

WHERE       A.type = 'U'

GROUP BY    A.schema_id, A.Name

GO

Result Set:

Person.Address                    78456

Person.AddressType                18

dbo.AWBuildVersion                1

dbo.BCPTest                       5

Production.BillOfMaterials        8037

Person.BusinessEntity             41554

Person.BusinessEntityAddress      78456

However, for views row count is not available in sys.partitions. To get the row count for a view; you must query the view itself.

USE   [AdventureWorks2008R2]

GO

 

SELECT COUNT(*) FROM HumanResources.vEmployee

GO

Result Set:

290

 

(1 row(s) affected)

This can be encapsulated in a stored procedure to query all available views and then display the result set. The procedure can be created as:

USE   [AdventureWorks2008R2]

GO

 

CREATE PROCEDURE dbo.ViewsRowCount

AS

BEGIN
SET NOCOUNT ON

CREATE TABLE #tempRowCount

(

      Name        VARCHAR(100),

      Row_Count   INT

)

 

DECLARE     @SQL VARCHAR(MAX)

SET         @SQL = ''

SELECT @SQL = @SQL + 'INSERT INTO #tempRowCount SELECT ''' +

            SCHEMA_NAME(schema_id) + '.' + name + ''', COUNT(*) FROM ' +

            SCHEMA_NAME(schema_id) + '.' + name +

            CHAR(13) FROM sys.objects WHERE type = 'V'

EXEC (@SQL)

 

SELECT      Name, Row_Count

FROM        #tempRowCount

END

GO

Once created this stored procedure returns row count for all views in database as bellow:

USE   [AdventureWorks2008R2]

GO

 

EXEC  dbo.ViewsRowCount

GO

Result Set:

Name                           Row_Count

dbo.vApplicationSpecialists    3

Person.vAdditionalContactInfo  10

HumanResources.vEmployee       290

Sales.vIndividualCustomer      18508

Sales.vPersonDemographics      19972

HumanResources.vJobCandidate   13

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe


Filed under: SQLServer
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.