Monday, May 5, 2008

Getting row count of table in SQL Server 2005

It is much easier in SQL Server 2005 to get row count per table.

SELECT
[TableName]=tbl.name,
[RowCount] = SUM
(
CASE
WHEN (pt.index_id < 2) AND (au.type = 1) THEN pt.rows
ELSE 0
END
)
FROM
sys.tables tbl
INNER JOIN sys.partitions pt
ON tbl.object_id = pt.object_id
INNER JOIN sys.allocation_units au
ON pt.partition_id = au.container_id
GROUP BY
tbl.name ORDER BY [RowCount]DESC;

2 comments:

Unknown said...

I have another similar but another syntax at Malcan Rows counts - getting quickly, comparing databases. You may also find there the join query that gives you the difference of rows per table between two databases.

Unknown said...

Beautiful... This code is based on updated version of sys objects of sql2k5/sql2k8. I think one should ignore the link posted by Artur, as that link uses the old sql2k objects.