查看并分析数据表所占的空间

--1.新增TEMP TABLE
create   table   #temptable   
([name] varchar(50),[rows] bigint,resverved varchar(50),data varchar(50),index_size varchar(50),unused varchar(50))  

--2.执行查询结果
select 'insert into #temptable exec sp_spaceused '''+name+'''' 
from sysobjects where objectproperty(id,'IsUserTable')=1

--3. 方便查看
update #temptable 
set resverved=left(resverved,charindex(' ',resverved)-1),
data=left(data,charindex(' ',data)-1),
index_size=left(index_size,charindex(' ',index_size)-1),
unused=left(unused,charindex(' ',unused)-1)

--4.查询结果
select * from #temptable  
order by cast(resverved as bigint) desc,cast(data as bigint) desc
---5. 为了方便比较，汇总
select convert(char(5),sum(cast(data as bigint))/1024/1024)+'GB' 
from #temptable


select convert(char(5),sum(cast(unused as bigint))/1024)+'MB' 
from #temptable

select convert(char(5),sum(cast(resverved as bigint))/1024)+'MB' 
from #temptable

---有了上面的结果，对每个表进行容量回收，清楚垃圾数据，还可以考虑分离数据库。


--drop table #temptable

 

转载于:https://www.cnblogs.com/GerryGe/p/5114024.html