Sqlserver and XML

Find row count of one or more tables

Posted on January 5th, 2008 | by Admin |

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