When working with SQL Server, it is often necessary to determine the number of rows in a table. The most common approach is to use the SELECT COUNT(*)
statement, but this method has its limitations. Firstly, it requires a table scan, which can be time-consuming and resource-intensive for large tables. Secondly, it does not provide an easy way to obtain row counts for all tables in a database.
Fortunately, SQL Server provides a system function called sp_spaceused
that can help overcome these challenges. When executed without any parameters, sp_spaceused
returns usage information about the current database. However, when provided with a specific object name, such as a table name, it also returns the number of rows along with the amount of space used by the table and its indexes.
Under the hood, sp_spaceused
retrieves row count information from system objects, such as the sysindexes
table in SQL Server 2000 or the sys.dm_db_partition_stats
DMV in SQL Server 2005 and later versions. Since the counts are obtained from system objects, there is no need for a table scan, solving the first problem.
To address the second problem of obtaining row counts for all tables in a database, you can either use a cursor to iterate through each table and call sp_spaceused
or directly query the system objects. Here are examples of both approaches:
Row Counts Using sysindexes (SQL Server 2000)
-- Shows all user tables and row counts for the current database -- Remove OBJECTPROPERTY function call to include system objects SELECT o.NAME, i.rowcnt FROM sysindexes AS i INNER JOIN sysobjects AS o ON i.id = o.id WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0 ORDER BY o.NAME
Row Counts Using DMVs (SQL Server 2005 and later)
-- Shows all user tables and row counts for the current database -- Remove is_ms_shipped = 0 check to include system objects -- i.index_id < 2 indicates clustered index (1) or hash table (0) SELECT o.name, ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME
It is important to note that the row count information obtained from these system objects is not dependent on updated statistics. This means that even if statistics become outdated, the row counts will remain accurate. To verify this, you can compare the counts obtained using the SELECT COUNT(*)
method with the counts from the system objects. In most cases, they should match perfectly.
In conclusion, when you need to determine the number of rows in a SQL Server table, it is recommended to use the system objects sysindexes
or sys.dm_db_partition_stats
instead of the SELECT COUNT(*)
method. This approach eliminates the need for a table scan and provides accurate row counts for all tables in the database.