Find disk space usage for all tables and their indexes

The environment: SQL Server 2008 (this should work on SQL Server 2005 as well)

Consider a scenario in which you inherit a large database, or in which you revisit a database which has been sitting quietly in production for a while, just accumulating data.  This script compiles a full list of all of the tables in the database, along with their total reserved size in KB, their total index size in KB, and their total data size in KB.

create table #tmpsizes
(
    ID            int    identity(1,1) primary key,
    [name]        nvarchar(128),
    [rows]        char(11),
    reserved      varchar(18),
    data          varchar(18),
    index_size    varchar(18),
    unused        varchar(18),
)

create table #tmpsizes2
(   
    ID                int    identity(1,1) primary key,
    [name]            nvarchar(128),
    [rows]            int,
    reserved_kb       int,
    data_kb           int,
    index_size_kb     int,
    unused_kb         int
)

declare @objname nvarchar(128)
declare sizeCur cursor forward_only for
    select '[' + s.[name] + '].' + t.[name]
    from sys.tables t
        inner join sys.schemas s on t.schema_id = s.schema_id
open sizeCur
fetch next from sizeCur into @objname
while @@fetch_status = 0
begin
    insert into #tmpsizes
        exec sp_spaceused @objname

    fetch next from sizeCur into @objname
end
close sizeCur
deallocate sizeCur

insert into #tmpsizes2
    select [name],
        [rows],
        left(reserved, len(reserved)-3),
        left(data, len(data)-3),
        left(index_size, len(index_size)-3),
        left(unused, len(unused)-3)
    from #tmpsizes
 
-- show tables with the most rows at the top of the list      
select *
from #tmpsizes2
order by [rows] desc

-- show tables with highest ratios of index size to table size 

-- at the top of the list
select *,
    cast(index_size_kb as decimal(18,4))/cast(reserved_kb as decimal(18,4)) as index_percentage
from #tmpsizes2
where reserved_kb > 0
order by cast(index_size_kb as decimal(18,4))/cast(reserved_kb as decimal(18,4)) desc

drop table #tmpsizes
drop table #tmpsizes2