设为首页收藏本站我的广告

运维网

 找回密码
 注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
运维网 首页 数据库运维 SQL Server 查看内容

SQL Server查看所有表大小,所占空間

2012-4-27 13:37| 发布者: yunweiw.com| 查看: 660| 评论: 0|原作者: 运维网|来自: 网络

  ——主要原理: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

下一篇:SQL 格式化工具 V.1

上一篇:sql Split函数


鲜花

握手

雷人

路过

鸡蛋
加入阿里云推荐返利15%

最新评论

QQ|申请友链|sitemap|手机版|小黑屋|Archiver|运维网 ( 京ICP备16008201号  

GMT+8, 2016-12-4 03:46 , Processed in 0.088886 second(s), 28 queries , Xcache On.

Powered by Discuz! X3.2 Licensed

© 2001-2013 Comsenz Inc.

返回顶部