Home > SQLServer > SQL Server – Row count for all views / tables

SQL Server – Row count for all views / tables

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

About these ads
Categories: SQLServer
  1. John Ardmore
    March 15, 2012 at 2:23 pm

    Hi,
    very nice people start using sys.objects and sys.partitions

    but, i think the following sql statement ( first in this post )
    (
    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
    )

    will not return the true row count for all tables, but the last seed values for identity of each table

    for example, I have a table of Orders with near 2.000.000 rows.
    This table is under very intensive activity ( inserts, updates and deletes are happening more than 100 times within every second )
    so after I execute the above statement, it gave me near 900.000.000 rows for Orders table !

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 279 other followers

%d bloggers like this: