Get rowcount of all the tables...

IF object_id('SP_GetAllTablesRowCount') IS NOT NULL
BEGIN
PRINT 'Procedure SP_GetAllTablesRowCount dropped'
DROP PROCEDURE SP_GetAllTablesRowCount
END
Go

/***********************************************************************************************************
Procedure Name : SP_GetAllTablesRowCount
Author Name : Ramkumar Murugesan
Creation Date : 29 March 2006

Parameters
----------
None

Description
-----------
Calculates the rowcount of all the tables in the current database

Execution
---------
1) EXEC SP_GetAllTablesRowCount
***********************************************************************************************************/

CREATE PROCEDURE SP_GetAllTablesRowCount
AS

DBCC UPDATEUSAGE (0)

SELECT object_name(id) TableName
,max(rowcnt) Rows
FROM sysindexes
WHERE object_name(id) IN (SELECT Name
FROM sysobjects
WHERE type = 'u')
GROUP BY object_name(id)

RETURN

Go
IF @@error = 0
PRINT 'Procedure SP_GetAllTablesRowCount created'
Go


Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

wont that DBCC UPDATEUSAGE (0) suffice???

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Good script but a I have few notes:

1. There is no need to use MAX() and GROUP BY. The row count for a heap or clustered index is always the maximum number. No non-clustred index would ever have more rows than the underlying heap/clustered index.

2. You could avoid looking up the type of object ('U') in sysobjects. In SQL Server 2000, all 'user' tables have ids greater than 1977058078. Granted system tables used in replication will have larger ids but you can filter those out if necessary.

So that leaves you with:


DBCC UPDATEUSAGE (0)
SELECT object_name(id) TableName,rowcnt Rows
FROM sysindexes
WHERE id>1977058078 AND indid IN (0,1)


Please compare this and the results you get from your procedure.

Nathan H. Omukwenyi
MVP [SQL Server]

I prefer type = 'u' test over id > whatever number is, because the performance of selecting rows from sysindexes table is not really that important.
Restricting indid to 0 - heap and 1 - clustered index is good idea that makes the query so much simpler.

Just trying to provide another way of looking at the same issue. Forgive me for thinking about performance and trying to avoid going through the often larger sysobjects table. I can be obsessive sometimes.

Nathan H. Omukwenyi
MVP [SQL Server]

Nathan, no need to get upset, if you are, not quite sure. I just thought we were a bit too hard on Ram, so I wanted to mention something he did right IMO.

Thanks VBK for your suggestions. as MM mentioned, i would prefer type = u than id > though i didnt think of that option.

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."