SQLSERVER计算表的空间使用量

使用sp_MSforeachtable+sp_spaceused

create table #t(name varchar(255),rows bigint,reserved varchar(20),data varchar(20),index_size varchar(20),unused varchar(20))
exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'"
select * from #t

---

使用游标+sp_spaceused

--drop table #tblspace
set nocount on
declare @db varchar(20)
set @db = db_name()
dbcc updateusage(@db) with no_infomsgs
go

create table #tblspace
(
数据表名称 varchar(50) null,
记录笔数 int null,
保留空间 varchar(15) null,
数据使用空间 varchar(15) null,
索引使用空间 varchar(15) null,
未使用空间 varchar(15) null,
)

declare @tblname varchar(50)
declare curtbls cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open curtbls
Fetch next from curtbls into @tblname
while @@fetch_status = 0
begin
insert #tblspace exec sp_spaceused @tblname
fetch next from curtbls into @tblname
end
close curtbls
deallocate curtbls

select * from #tblspace order by
convert(int,left(保留空间,len(保留空间)-2)) desc
drop table #tblspace