Database Size Script

This just executes sp_spaceused on every table in a database, and plops the results into a temp table so you can query the data altogether.

declare 
    @TableName varchar(128),
    @RID int,
    @MaxRID int,
    @SQL nvarchar(max)
if object_id('tempdb.dbo.#LoopSrc') is not null drop table #LoopSrc
create table #LoopSrc
(
    RID int identity(1,1) primary key clustered,
    TableName varchar(128)
)

if object_id('tempdb.dbo.#Tabs') is not null drop table #Tabs
create table #Tabs
(
    TableName varchar(128),
    nRows int,
    nReserved as cast(replace(sReserved, ' KB', '') as int),
    nData as cast(replace(sData, ' KB', '') as int),
    nIndexSize as cast(replace(sIndexSize, ' KB', '') as int),
    nUnused as cast(replace(sUnused, ' KB', '') as int),
    sReserved varchar(30),
    sData varchar(30),
    sIndexSize varchar(30),
    sUnused varchar(30)

)
/*****************************
*** INSERT LOOP ITEMS HERE ***
*****************************/
insert into #LoopSrc
(
    TableName
)
select name
from sys.tables

select 
    @RID = 1,
    @MaxRID = @@rowcount
/**********************
*** LOOP STRUCTURE  ***
**********************/
while @RID <= @MaxRID
    begin

        select 
            @TableName = TableName,
            @sql = 'exec sp_spaceused ' + TableName
        from #LoopSrc
        where RID = @RID
        begin try
            insert into #Tabs
            exec sp_executesql @sql
        end try 
        begin catch
        end catch

        select @RID += 1
    
    end


select *
from #Tabs
order by nRows desc