Zielyn's Sql Blog

SELECT COUNT(1)
FROM dbo.[Table]

This has to be the simplest and most accurate way to get the Row Count. It is also the most intensive way since it has to do a table or clustered index scan to achieve the results. While not that bad for anything below a few million, anything above that it starts to get a little hectic. Trying to count a billion row table, it’ll take too long to get results and use up way to many resources.

This is for SQL Server 2000

As long as sysindexes and sysobjects are available tables in SQL server, this method will work. Currently it works up to version SQL Server 2008 R2, but that is no guarantee for the future. In moving to system views in later versions of SQL Server, you should use system views instead.

SELECT so.name AS [Table Name],
    rows AS [RowCount]
FROM sysindexes AS si
    join sysobjects AS so on si.id = so.id
WHERE indid IN (0,1)
    AND xtype = 'U'

Remember, these are not accurate counts, but give the best estimate on the server besides physically counting it with the first script provided above. While they aren’t accurate, they are damn close. One of these days, I’ll have to play around with huge tables to see how accurate these counts really are.
An indid of 0 means there are no indexes on the table and it is a heap table. An indid of 1 is the first index on the table. Usually this is the clustered index. You can query sysindexes or sys.indexes in future versions to figure out what index has an id of 1 if you want to know which index is being used for the row count.

The xtype where clause only gets the row counts for any user created tables. If you want to get a row count of the system tables as well, you can modify the where clause to the following:

WHERE si.indid IN (0,1)
    AND si.xtype in ('U','S')

This is for SQL Server 2005 and above

SELECT SUM(rows) as RowCount,
    OBJECT_NAME(OBJECT_ID) as [Table Name]
FROM sys.partitions as p
WHERE index_id in (0,1)
GROUP BY OBJECT_ID

Mostly the same code, but in a different place. It gets a rough count and actually lets me know a rough idea of how many rows are in every table of that database. According to Sql Server Books Online, this is only an approximate row count, so keep that in mind if basing any mission critical scripts off of it. While only an approximate, it does provide you with a good sense of how big the table is. The reason for including the where clause for index_id is because you want to make sure you are only geting a single count for each table. sys.partitions keeps count on all of the indexes and partitions for a particular table.

Including SUM(rows) is for when you are working with Sql Server Enterprise Edition. Enterprise Edition allows you to partition tables for better availability. When you partition a table, sys.partitions keeps an approximate number of how many rows are in each partition. Since we only want the row count, we want to add up all the partitions to get that close approximate count of the table.

Since sys.partitions has a scope of the the current database, you have to be a little fancy in getting the Row Count if you want to get all of the table’s row counts.

IF (OBJECT_ID('Tempdb..##Temp') is not null)
    DROP TABLE ##Temp

CREATE TABLE ##Temp (
    DatabaseName varchar(255),
    TableName varchar(255),
    [RowCount] bigint
)

INSERT INTO ##Temp (
    DatabaseName,
    TableName,
    [RowCount]
)
EXEC sp_MSforeachdb @command1 = 'USE ?;
SELECT ''?'' AS [Database Name],
    OBJECT_NAME(OBJECT_ID) as [Table Name],
    SUM(rows) as [Row Count]
FROM sys.partitions AS p
WHERE index_id in (0,1)
    AND DB_ID() > 4
GROUP BY OBJECT_ID'

SELECT DatabaseName,
    TableName,
    [RowCount]
FROM ##Temp

DROP TABLE ##Temp

This method of putting the results into a temp table allows you to work with the data easier. If you ran the sp_MSForeachdb sproc without this method, it would spit out a result set for every database on your server. The temp table brings it all back in one table and one result set. Every time the sproc changes database, the scope of a regular temp table is lost thus why you need the global temp table. Remember to clean up after you are done working with the global temp table, as it will stay around as long as a user is connected and using that table. Drop it as soon as you are finished with it so nothing else is tempted to use it.

I added an extra where clause in the statement to not get the row counts of the system databases which default to the database ids starting from 1. You can remove that line to get all the table counts, but the only values that will really change are the row counts of TempDB and possibly after a system update.

You can also use the SQL Server 2000 script, provided here, with the sp_MSforeachdb sproc to get all the row counts in the system on a SQL Server 2000 server.

In SQL Server Management Studio 2008, you don’t have to even code at all! Connecting to a server of SQL Server 2005 and above allows you to see the row count with relative ease. When connected to a server, press F7 or using the menu View -> Object Explorer Details. Click to the database you want to view, then to the tables list. This should provided you with the row count of every table! If I’m mistaken and that is not the default view, right click on the header and select “Row Count.” There’s also a lot more information there than Management Studio 2005, so play around.

Hope this helps you find the row count to tables faster