How to list the sizes of the tables in a SQL Server database

Posted at 18:12 on 14 December 2007

Scott Mitchell gave some instructions on 4GuysFromRolla a while back on how to list the sizes of all the tables in a SQL Server database.

His solution uses a mixture of SQL and ASP.NET, but some people will want an alternative in pure SQL. So, with no further ado, here you go:

create procedure sp_get_table_usage
as
begin
    create table #t (
        name varchar(100),
        rows int,
        reserved varchar(100),
        data varchar(100),
        index_size varchar(100),
        unused varchar(100)
    )
 
    declare @name varchar(100)
 
    declare c cursor
        for select name from sysobjects where type='U'
 
    open c
    fetch next from c into @name
    while @@FETCH_STATUS = 0
    begin
        insert into #t
            exec sp_spaceused @name
        fetch next from c into @name
    end
    close c
    deallocate c
    update #t
        set reserved = rtrim(replace(reserved, 'KB', '')),
            data = rtrim(replace(data, 'KB', '')),
            index_size = rtrim(replace(index_size, 'KB', '')),
            unused = rtrim(replace(unused, 'KB', ''))
 
    alter table #t
        alter column reserved int
    alter table #t
        alter column data int
    alter table #t
        alter column index_size int
    alter table #t
        alter column unused int
 
    select * from #t
        order by name
end

You can change the sort order by changing the order by clause at the end. For instance, order by data desc will list them in descending order of size.