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 in the table), which can really impact performance including flushing various system and database caches.

You can also directly query the statistics, but this is only an approximation and depends on how recently the statistics were updated (with DBCC UPDATE STATISTICS). 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,
   sysidexes 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. And of course, in SQL Server 2005 and SQL Server 2008 there are various Dynamic Management Views (DMVs) you can use to pull related data along with this information.