sqlserver查看表空間

sqlserver 用於查看當前資料庫所有表占用空間大小的存儲過程

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