SQL Server查看表占用的空间

Hotzenplotz

于 2018-07-19 13:57:01 发布

阅读量869

点赞数

分类专栏： MSSQL

MSSQL 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一种在SQL Server中统计每个表空间使用情况的方法。通过创建临时表并利用游标遍历所有用户表，执行sp_spaceused存储过程来收集各表的空间数据，包括行数、保留空间、数据空间等，并将原始数据转换为整数类型方便比较。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))

declare @name varchar(100) declare cur cursor for select name from sysobjects where xtype='u' order by name open cur fetch next from cur into @name while @@fetch_status=0 begin insert into #data exec sp_spaceused @name print @name fetch next from cur into @name end close cur deallocate cur create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int) insert into #dataNew select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data, convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data select * from #dataNew order by data desc