SQL查看所有表的大小
2024-09-04 01:11:39
--查看所有表的大小
declare @id int declare @type character(2) declare @pages int declare @dbname sysname declare @dbsize dec(15,0) declare @bytesperpage dec(15,0) declare @pagesperMB dec(15,0) create table #spt_space ( [objid] int null, [rows] int null, [reserved] dec(15) null, [data] dec(15) null, [indexp] dec(15) null, [unused] dec(15) null ) set nocount on -- Create a cursor to loop through the user tables declare c_tables cursor for select id from sysobjects where xtype = 'U' open c_tables fetch next from c_tables into @id while @@fetch_status = 0 begin /* Code from sp_spaceused */ insert into #spt_space (objid, reserved) select objid = @id, sum(reserved) from sysindexes where indid in (0, 1, 255) and id = @id select @pages = sum(dpages) from sysindexes where indid < 2 and id = @id select @pages = @pages + isnull(sum(used), 0) from sysindexes where indid = 255 and id = @id update #spt_space set data = @pages where objid = @id /* index: sum(used) where indid in (0, 1, 255) - data */ update #spt_space set indexp = (select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id) - data where objid = @id /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */ update #spt_space set unused = reserved - ( select sum(used) from sysindexes where indid in (0, 1, 255) and id = @id ) where objid = @id update #spt_space set [rows] = i.[rows] from sysindexes i where i.indid < 2 and i.id = @id and objid = @id fetch next from c_tables into @id end select TableName = (select left(name,60) from sysobjects where id = objid), [Rows] = convert(char(11), rows), ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'), DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'), IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'), UnusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB') from #spt_space, master.dbo.spt_values d where d.number = 1 and d.type = 'E' order by reserved desc drop table #spt_space close c_tables deallocate c_tables
最新文章
- js获取客户端计算机硬件及系统信息
- windows系统调用 线程 启动与挂起
- 通知---iOS
- 模拟Vue之数据驱动3
- 开源一款iOS中国地图行政区控件(含一级与二级行政区)
- HTTP高并发调优小记
- Java 实现TCP/IP协议的收发数据(服务端)
- 牛客网 223C 区区区间间间(单调栈)
- call()的个人理解
- 550 5.7.1 Client does not have permissions to send as this sender
- 最长公共前缀的golang实现
- BZOJ.2069.[POI2004]ZAW(最短路Dijkstra 按位划分)
- C语言转义字符基础总结
- HDU 5834 Magic boy Bi Luo with his excited tree(树形dp)
- Bluez相关的各种tools的使用
- 20155212 2016-2017-2 《Java程序设计》第7周学习总结
- ElasticSearch异常归纳(能力工场小马哥)
- Android中自动跳转
- .net remoting和wcf自托管——一个bug引发的警示
- 远程服务器上的weblogic项目管理(四)filelock not found错误解决方法
热门文章
- 编程语言 - 大数据 - Hadoop
- 关于VMware中的几个网络模式
- VulnStack靶场实战(未完成)
- 记boost协程切换bug发现和分析
- 诊断:MRP0: Background Media Recovery process shutdown with error ORA-19909
- CentOS7没有ifconfig/route/arp/netstat等命令的解决方案
- GNU MAKE 笔记
- smarty中判断数组是否为空的方法
- grep命令用关系或查询多个字符串
- IIS故障 应用程序池“XXX”提供服务的进程在与 Windows Process Activation Service 通信时出现严重错误。该进程 ID 为“XXXX”。数据字段包含错误号。