"how to find largest objects in a sql server database?" Code Answer

i've been using this sql script (which i got from someone, somewhere - can't reconstruct who it came from) for ages and it's helped me quite a bit understanding and determining the size of indices and tables:

select 
    t.name as tablename,
    i.name as indexname,
    sum(p.rows) as rowcounts,
    sum(a.total_pages) as totalpages, 
    sum(a.used_pages) as usedpages, 
    sum(a.data_pages) as datapages,
    (sum(a.total_pages) * 8) / 1024 as totalspacemb, 
    (sum(a.used_pages) * 8) / 1024 as usedspacemb, 
    (sum(a.data_pages) * 8) / 1024 as dataspacemb
from 
    sys.tables t
inner join      
    sys.indexes i on t.object_id = i.object_id
inner join 
    sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
inner join 
    sys.allocation_units a on p.partition_id = a.container_id
where 
    t.name not like 'dt%' and
    i.object_id > 255 and  
    i.index_id <= 1
group by 
    t.name, i.object_id, i.index_id, i.name 
order by 
    object_name(i.object_id)

of course, you can use another ordering criteria, e.g.