From time to time, I need to find the largest tables in terms of rows / used space / total space etc. I originally used sp_spaceused, a temp table and a cursor (or sp_MSForEachTable) and put them into a stored procedure to get the information I need.

But now as I am working more on table partition management, I find there is a DMV that can give me the exactly info I need, and what surprises me is that sp_spaceused is actually using this DMV, i.e. sys.dm_db_partition_stats to get the data as well. So here is the one sql to get the largest tables in a db (only applicable to sql server 2k5 / 2k8 for tables without xml indexes)

-- find the table size info (no xml index) using sys.dm_db_partition_stats
-- Author: Jeffrey Yao
-- Date: Sept 27, 2010

select name=object_schema_name(object_id) + '.' + object_name(object_id)
, rows=sum(case when index_id < 2 then row_count else 0 end)
, reserved_kb=8*sum(reserved_page_count)
, data_kb=8*sum( case
     when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
     else lob_used_page_count + row_overflow_used_page_count
    end )
, index_kb=8*(sum(used_page_count)
    - sum( case
           when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
        else lob_used_page_count + row_overflow_used_page_count
        end )
     )    
, unused_kb=8*sum(reserved_page_count-used_page_count)
from sys.dm_db_partition_stats
where object_id > 1024
group by object_id
order by
rows desc
-- data_kb desc
-- reserved_kb desc
-- data_kb desc
-- index_kb desc
-- unsed_kb desc