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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/28299.html
標籤：SQL Server
上一篇：本地庫還原至阿里云RDS服務器
下一篇：sqlserver實作分隔字串