Get table and row size data

It often happens that you need to know how big a table is. There are many reasons for this: finding which tables have the most rows; understanding which tables tend to grow the fastest; find which table is using the most space.

I found a nice query on stackexchange.com that listed the tables, row counts and data sizes in a database:


I added a few things to give me a little more information, namely showing the total and used space in KB, MB and GB. In addition I added an average row size column to give me a quick view into which table(s) were the heaviest. If you use either of these queries on a system database you will need to comment out the WHERE clause.
The first script below shows the total size of the table and all its indexes. The second script groups by index. The third script shows the table data only, i.e. either the size of the HEAP or Clustered Index. Finally, the fourth script handles partitioned tables (there is a slight discrepancy with row counts between this script and the other ones).


I've added this script to my locally saved version of the DMV Starter Pack