UPDATEUSAGE

DBCC UPDATEUSAGE
报告和更正 sysindexes 表的不正确内容，该内容可能会导致通过 sp_spaceused 系统存储过程产生不正确的空间使用报表。

set nocount on
declare @db varchar(20)
set @db=db_name()
dbcc updateusage(@db) with no_infomsgs
go
create table #tblspace
(数据表名称 varchar(50) null,
数据行数 int null,
保留空间 varchar(15) null,
数据使用空间 varchar(15) null,
索引使用空间 varchar(15) null,
 末使用空间 varchar(15) null
)
declare @tblname varchar(50)

declare curtbls cursor for
 select table_name from information_schema.tables
where table_type='base table'

open curtbls
fetch next from curtbls into @tblname

while @@fetch_status=0
 begin
  insert #tblspace exec sp_spaceused @tblname
fetch next from curtbls into @tblname
end
close curtbls
deallocate curtbls

select * from #tblspace order by
convert(int, left(保留空间,len(保留空间)-2)) desc
drop table #tblspace

--------------------------

数据表名称                                              数据行数        保留空间            数据使用空间          索引使用空间          末使用空间           
-------------------------------------------------- ----------- --------------- --------------- --------------- --------------- 
tblmntquote                                        528000      34440 KB        34384 KB        8 KB            48 KB
test5                                              528000      23688 KB        23664 KB        8 KB            16 KB
tb2038                                             1569        584 KB          520 KB          8 KB            56 KB
tb_1001                                            1680        520 KB          496 KB          8 KB            16 KB
222                                                61          24 KB           16 KB           8 KB            0 KB
Customers                                          19          24 KB           8 KB            16 KB           0 KB
Products                                           7           24 KB           8 KB            16 KB           0 KB
Sales                                              32          24 KB           8 KB            16 KB           0 KB
Employee                                           3           16 KB           8 KB            8 KB            0 KB
Expenses                                           14          16 KB           8 KB            8 KB            0 KB
aa                                                 3           16 KB           8 KB            8 KB            0 KB
bb                                                 3           16 KB           8 KB            8 KB            0 KB
test                                               1           16 KB           8 KB            8 KB            0 KB
test2                                              7           16 KB           8 KB            8 KB            0 KB
testt                                              6           16 KB           8 KB            8 KB            0 KB
test6                                              0           0 KB            0 KB            0 KB            0 KB
dtproperties                                       0           0 KB            0 KB            0 KB            0 KB

本文转自 boyi55 51CTO博客，原文链接：http://blog.51cto.com/boyi55/44024，如需转载请自行联系原作者