sqlserver 用于查看当前数据库所有表占用空间大小的存储过程 create procedure dbo.proc_getsize as begin create table #temp ( t_id int primary k
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
--结束END--
本文标题: sqlserver查看表空间
本文链接: https://www.lsjlt.com/news/2457.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0