Find row count of one or more tables

The following code can be used to retrieve the rowcount of each tables in the database. Let us create a function (TABLE VALUED) which returns the row count of all the tables.

 

CREATE FUNCTION dbo.GetRowCount()

RETURNS TABLE

RETURN

    SELECT

    st.Name,

    SUM( 

        CASE

            WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows 

            ELSE 0 

        END

       ) AS Rows

    FROM sys.partitions p

    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

    INNER JOIN sys.tables st ON st.object_id = p.Object_ID

    GROUP BY st.name 

 

Execute the function as follows:

-- list all tables and the number of rows

SELECT * FROM dbo.GetRowCount()

 

-- find the rowcount of a single table

SELECT * FROM dbo.GetRowCount()

WHERE Name = 'Invoices'

 

-- find the rowcount of more than one table

SELECT * FROM dbo.GetRowCount()

WHERE Name IN ('Invoices','Orders')

 

If you have organized your tables into multiple schemas then you might need a different version of the function. Here is a modified version which retrieves the schema information too.

CREATE FUNCTION dbo.GetRowCount()

RETURNS TABLE

RETURN

    SELECT

    sch.name AS SchemaName,

    st.Name AS TableName,

    sch.name + '.' + st.name AS QualifiedName,

    SUM( 

        CASE

            WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows 

            ELSE 0 

        END

       ) AS Rows

    FROM sys.partitions p

    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

    INNER JOIN sys.tables st ON st.object_id = p.Object_ID

    INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id

    GROUP BY st.name, sch.name