SQL Server 2000: how do i get a list of tables and the row counts?

Question

I know that I can get a list of tables with

SELECT TABLE_NAME FROM information_schema.tables 
WHERE NOT TABLE_NAME='sysdiagrams' 
  AND TABLE_SCHEMA = 'dbo' 
  AND TABLE_TYPE= 'BASE TABLE'

But I'm not sure how to modify that to get a 2nd column with the current count of rows for the tables. I though of something like this:

DECLARE @tbl VARCHAR(200)
(SELECT @tbl = TABLE_NAME, TABLE_NAME,
(SELECT COUNT(ID) AS Cnt FROM @tbl)
FROM information_schema.tables 
WHERE NOT TABLE_NAME='sysdiagrams' 
  AND TABLE_SCHEMA = 'dbo' 
  AND TABLE_TYPE= 'BASE TABLE')

I know the above is not valid T-SQL but I think it gets the point of what I would like the have done. This is for SQL Server 2000. I would prefer not to use store procedures if at all possible.

Answer 1

A quick and dirty way (includes uncommitted changes and possibly forwarding pointers on heaps)

select o.name, rows 
from sysindexes i join sysobjects o on o.id=i.id
where indid < 2 and type='U'