Table Size in Microsoft SQL Server

Sometimes I want to find out how many rows are in each user table in a SQL Server 2000 database. The following SQL helps me determine this.

select
    'SELECT
    COUNT(*) AS ' +
    '''' +
    RTRIM ( name ) +
    '''' +
    ' FROM ' +
    RTRIM ( name )
from
    sysobjects
where
    type = 'U'
order by
    name

Note: Running this on very large tables or in a database with lots of tables will have a significant negative impact on server performance. This forces a table scan of each table (reading each record).

You can also directly query the statistics, but this is only an approximation and depends on how recently the statistics were updated. Looking at the sysindexes table (again, this is SQL 2000), you can look at the rowcnt field and match the id field up against the sysobjects table. The following SQL may provide a good approximation of the rows in each user table.

SELECT
    o.name,
    i.name,
    i.rowcnt
FROM
    sysobjects o,
    sysindexes i
WHERE
    i.id = o.id AND
    o.type = 'U' -- User Tables

I have some better SQL I'll have to track down and add to this at some later date to show additional information.