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 TableName,i.Rows NumberOfRows
FROM sys.tables T
JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE indid IN (0,1)

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 :


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.