最近客户的数据库暴涨,一开始找不到原因,用sql查询表的空间大小,结果之前用的sql语句只能查询除了text,image外的字段大小.搞得一时间混乱不堪.后来咨询了大师们,找到用sp_spaceused这个系统函数可以查询表的真实大小,包含text等字段.sql如下:
declare
@id NVARCHAR(100)
create
table
#spt_space
(
[
name
] NVARCHAR(50)
null
,
[
rows
]
int
null
,
[reserved] NVARCHAR(50)
null
,
[data] NVARCHAR(50)
null
,
[index_size] NVARCHAR(50)
null
,
[unused] NVARCHAR(50)
null
)
set
nocount
on
declare
c_tables
cursor
for
select
name
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 ([
name
],[
rows
],reserved,data,index_size,unused)
EXEC
sp_spaceused @id
fetch
next
from
c_tables
into
@id
end
SELECT
*
FROM
(
SELECT
*,
CAST
(
REPLACE
(reserved,
'KB'
,
''
)
AS
INT
)
AS
reservedindex
FROM
#spt_space
) c
order
by
c.reservedindex
desc
drop
table
#spt_space
close
c_tables
deallocate
c_tables
|
本文转自温景良(Jason)博客园博客，原文链接：http://www.cnblogs.com/wenjl520/archive/2011/07/01/2095320.html如需转载请自行联系原作者
