List all tables in the database along with their row counts

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s