I am looking at ways to count rows in several tables on several databases. In this particular case I am looking for unused and abandoned items.

Several sources source1, source2 recommend using sys.dm_db_partition_stats everything agrees that the count is not perfectly accurate. MSDN describes row_count as

approximate number of rows in the partition.

I understand if the value is always changing, it might be off by a bit. There are some good arguments on why this small discrepancy really doesn't matter. But if the value is near zero or none, will it be accurate?

If I use sys.dm_db_partition_stats and I am getting no rows found, do I need to run a second query using COUNT (or something else) to make sure the table is really empty or is the inaccuracy limited to minor fluctuations over minor periods of time?

Clarify I don't own the data, so I am ethically prohibited from looking at it. If sys.dm_db_partition_stats tells me a table is empty I can't just go open it and see if it really is empty. I need to know if I can depend on the results returned from it. As pointed out in answers, more information than just this will be required, before deleting. The table may be used as a temp table, maybe it gets rows written to it and removed, and just always happens to be empty when I look at it.

  • If I use sys.dm_db_partition_stats and I am getting no rows found then you are probably in the wrong database. If you mean the rows column shows 0, rather than "no rows found" - which implies that the DMV returned no rows for your table - please clarify. Feb 22, 2016 at 17:08
  • @AaronBertrand I mean the rows column shows 0. Feb 22, 2016 at 17:54

2 Answers 2


If you're looking for unused or abandoned tables, the number of rows won't be enough. You'd better look into index usage stats:

select database_id, object_id, index_id, 
    last_user_seek, last_user_scan, last_user_lookup, last_user_update
from sys.dm_db_index_usage_stats

If you are interested in several methods for tracking table usage, I wrote about it some months ago.


I think source1 has the answer. He (Aaron) states that even when you get an "accurate" count, you could be blocking users trying to write to the table. As soon as you put your eyes on the result, it could be immediately inaccurate as writers were trying to put data in your table while you blocked them for the accurate count. So, I believe using partition_stats works fine for what you want. I wouldn't be afraid to test it though and it might be an interesting test at that. You could create a db of 1000 empty tables. Then a routine that constantly inserts and deletes data in random tables. Then run your partition query and run your count query and compare.
I think what might work best is to use partitions and accumulate your data in a table and over periods of days or weeks, any table with consistently zero results is unused or abandoned.

  • You have linked Source1 but referred to Aaron (Source2) in your answer Feb 22, 2016 at 15:17
  • Source2 Is by Aaron, Source1 by Jes, when talking about blocking users is talking about using COUNT, and is why it suggests using sys.dm_db_partition_stats instead. I concur that nothing should be done without additional data, just asking about the accuracy of this piece of data in low usage environment Feb 22, 2016 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.