To get the number of rows in a single table we can use the COUNT(*) or COUNT_BIG(*) functions, e.g.
COUNT(*) FROM Sales.Customer
This is quite straightforward for a single table, but quickly gets tedious if there are a lot of tables. Here
are a few ways of listing all the tables that exist in a database together with the number of rows they contain.
This script uses a stored procedure that is undocumented by Microsoft, though it is widely known about. It
will run a SQL statement against all tables in a database. As I want a single recordset, rather than one
recordset for every table I’ve inserted the results into a temporary table first. I’ve also used the COUNT_BIG
function which returns a bigint and was introduced in SQL Server 2000, if you know your rowcounts won’t exceed an
int or are using an earlier version then use COUNT(*) instead. The code is :
CREATE TABLE #RowCounts(NumberOfRows BIGINT,TableName VARCHAR(128))
EXEC sp_MSForEachTable 'INSERT INTO #RowCounts
COUNT_BIG(*) AS NumberOfRows,
''?'' as TableName
ORDER BY NumberOfRows DESC,TableName
DROP TABLE #RowCounts
One downside of this approach is that if the table sizes are large the code can end up table scanning and take a long time to complete.
There is a catalog view that will give us this information. The benefit of this approach is that it uses figures that have already
been calculated by SQL Server so it should run very quickly. Here is the script :
FROM sys.tables T
JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE indid IN (0,1)
BY i.Rows DESC,T.name
The script uses the clustered index to source the information (where indid=1). Tables that don’t have a clustered index are stored
as a heap but still have a row in sysindexes (where indid=0) containing the rowcount.
There is one important caveat, though, the figures may not be entirely accurate ! This is because SQL Server doesn’t always
keep this bang up to date. Fortunately there is a DBCC command which updates these figures. Just run this command before the
script above to make sure the figures are accurate :
sp_spaceused without parameters displays the disk space reserved and used by the whole database. However by specifying a table
name as the first parameter it will display the number of rows, disk space used and reserved by a table. We can use this with the
sp_MSForEachTable procedure mentioned above to get results for every table. An advantage to this approach is that it also shows the
space used each table (data and index).
reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
EXEC sp_MSForEachTable 'INSERT INTO
#RowCountsAndSizes EXEC sp_spaceused ''?'' '
SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,
CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName
Here is the result of the above query when run against the AdventureWorks database :
As with the previous approach it may be necessary to run 'DBCC UpdateUsage' to ensure that the results are accurate (this can also
be achieved by specifying 'updateusage' as the 2nd parameter to the stored procedure).