Counting Rows

I was travelling last week and didn't get a chance to post, so I'm going to post a "bonus" message today to make up for it. In fact, it was last week when I got an issue of the SQLServerCentral.com newsletter with an article that caught my eye. It was entitled How to Get Table Row Counts Quickly and Painlessly by Kendal Van Dyke. The article points out that the total row counts of all the tables in a database are stored in system tables that may be queried.

I am always interested in getting useful information out of the system tables, especially when it can provide a new perspective on the database itself. If you read my earlier blog entry on finding all the tables that contain particular column names, you probably know that already. The technique in the article is a way to look at all the row counts of all the tables in a list, which could be a great guage to the overall "size" of the database, and it could give insight to sluggish queries, if a table happened to be much larger than expected.

Of course it would be possible to write the simple query to count the rows of one table at a time, like this:

select count(*) from Shelby.NANames

This approach is valid, and it is still the only way to do a count on rows that meet particular criteria, because you can add a WHERE clause to limit the rows. The system table approach always yields the total number of rows, with no such filtering possible.

Nevertheless, getting an overview of the database rows from the system tables can still yield interesting, if not essential, information. Here is the code from Kendal's article, though I encourage you to sign up with sqlservercentral.com and read it for yourself as well.

-- 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

For Shelby v.5 users, this code can run straight from ShelbyQUERY, so there is no need to have any special querying software running to check out the database. Try it, and take a peek "behind the curtain" of the database.