我们在使用SQLSERVER数据库的过程中,通过数据库文件的属性,可以了解某个数据库所有表的数据、索引数据等占的空间,但是有时数据库中某个表所占的具体空间,我们有可能也需要了解,而前面说的这种方法就不能奏效了。SQLSERVER也的确提供了这样的功能,即:sp_spaceused存储过程,它可以方便的为我们统计出任意数据表的大小,其运行结果包括表的记录总数和空间分配、占用情况以及索引数据空间;另外,再结合sp_MSforeachtable这个存储过程,就可以统计一个数据中所有的表格的空间占用信息了。
以下是sp_spaceused和sp_MSforeachtable具体的使用示例:
- exec sp_spaceused '表名' (查询表所占空间)
- exec sp_spaceused '表名', true (更新表的空间大小,准确的表空大小)
- exec sp_spaceused (数据库大小查询)
- exec sp_MSforeachtable "exec sp_spaceused '?'" (所有用户表空间表小,SQL统计数据,,大量事务操作后可能不准)
- exec sp_MSforeachtable "exec sp_spaceused '?',true" (所有用户表空间表小,大数据库慎用)
运行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
drop table #t
示例看完了我们继续深入了解一下这两个存储过程:
参数说明:引用地址
1、sp_spaceused命令规范:
- sp_spaceused [[ @objname = ] 'objname' ] [,[ @updateusage = ] 'updateusage' ]
-
[ @objname=] 'objname'
请求其空间使用信息的表、索引视图或队列的限定或非限定名称。仅当指定限定对象名称时,才需要使用引号。如果提供完全限定对象名称(包括数据库名称),则数据库名称必须是当前数据库的名称。如果未指定 objname,则返回整个数据库的结果。
objname 的数据类型为 nvarchar(776),默认值为 NULL。
[ @updateusage=] 'updateusage'
指示应运行 DBCC UPDATEUSAGE 以更新空间使用信息。当未指定 objname 时,将对整个数据库运行该语句;否则,将对 objname 运行该语句。值可以是 true 或 false。updateusage 的数据类型为 varchar(5),默认值为 false。
2、sp_MSforeachtable使用说明
sp_MSforeachtable和sp_MSforeachdb微软提供的两个不公开的存储过程,从ms sql 6.5开始,存放在SQL Server的MASTER数据库中。
参数说明:
@command1 nvarchar(2000), --第一条运行的SQL指令
@replacechar nchar(1) = N'?', --指定的占位符号
@command2 nvarchar(2000)= null, --第二条运行的SQL指令
@command3 nvarchar(2000)= null, --第三条运行的SQL指令
@whereand nvarchar(2000)= null, --可选条件来选择表
@precommand nvarchar(2000)= null, --执行指令前的操作(类似控件的触发前的操作)
@postcommand nvarchar(2000)= null --执行指令后的操作(类似控件的触发后的操作)