To get the number of rows in a single table we can use the COUNT(*) or COUNT_BIG(*) functions, e.g.
SELECT
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.
Using sp_MSForEachTable
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
SELECT
COUNT_BIG(*) AS NumberOfRows,
''?'' as TableName
FROM ?'
SELECT TableName,NumberOfRows
FROM #RowCounts
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.
Using sysindexes
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 :
SELECT T.name
TableName,i.Rows NumberOfRows
FROM sys.tables T
JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE indid IN (0,1)
ORDER
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 :
DBCC
UPDATEUSAGE(0)
Using sp_spaceused
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).
CREATE TABLE
#RowCountsAndSizes (TableName
NVARCHAR(128),rows CHAR(11),
reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
unused 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
FROM #RowCountsAndSizes
ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName
DROP TABLE
#RowCountsAndSizes
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).