create procedure dbo.proc_getsize

as

begin

create table #temp

(

   t_id int primary key identity(1,1),

   t_name sysname,                        --表名

   t_rows int,                            --总行数

   t_reserved varchar(50),                --保留的空间总量

   t_data varchar(50),                    --数据总量

   t_indexsize varchar(50),               --索引总量

   t_unused varchar(50)                   --未使用的空间总量

)

exec SP_MSFOREACHTABLE N'insert into #temp(t_name,t_rows,t_reserved,t_data,t_indexsize,t_unused) exec SP_SPACEUSED ''?'''

select t_id,t_name,t_rows,t_reserved,t_indexsize,t_unused,t_data,

    case when cast(replace(t_data,' KB','') as float)>1000000 then cast(cast(replace(t_data,' KB','') as float)/1000000 as varchar)+' GB'

        when cast(replace(t_data,' KB','') as float)>1000 then cast(cast(replace(t_data,' KB','') as float)/1000 as varchar)+' MB'

    else t_data end as datasize

from #temp

order by cast(replace(t_data,' KB','') as float) desc

drop table #temp

end