查看 SqlServer 中查看数据库表大小

--查看 SqlServer 中查看数据库表大小 
create table #tb(表名 sysname,记录数 int ,保留空间 varchar(10),使用空间 varchar(10),索引使用空间 varchar(10),未用空间 varchar(10))
go
  insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''
  go
  
  select * from #tb
  go 
  
  drop table #tb
  go

--主要原理： 
 exec sp_spaceused '表名' --取得表占用空間 
 exec sp_spaceused ''--數據庫所有空間 
  
  
 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   

exec sp_MSForEachTable 
@precommand=N'
create table ##(
id int identity,
表名 sysname,
字段数 int,
记录数 int,
保留空间 Nvarchar(10),
使用空间 varchar(10),
索引使用空间 varchar(10),
未用空间 varchar(10))',
@command1=N'insert ##(表名,记录数,保留空间,使用空间,索引使用空间,未用空间) exec sp_spaceused ''?''

update ## set 字段数=(select count(*) from syscolumns where id=object_id(''?'')) 
where id=scope_identity()', @postcommand=N'select * from ## order by id drop table ##'