- 曙海教育集團論壇 (http://www.hufushizhe.com/bbs/index.asp)
-- SQL Server數據庫 (http://www.hufushizhe.com/bbs/list.asp?boardid=67)
---- 獲取SQL Server數據庫里表占用容量大小 (http://www.hufushizhe.com/bbs/dispbbs.asp?boardid=67&id=2519)
-- 發布時間：2010-12-13 13:02:13
-- 獲取SQL Server數據庫里表占用容量大小
其實只要使用系統內置的存儲過程sp_spaceused就可以得到表的相關信息
$ I4 B\' F7 G Z* ]! [
如：sp_spaceused \'tablename\'7 t0 P- Y! g# Q l( z6 V+ t+ t
以下是為了方便寫的一個存儲過程，目的是把當前的所有表的相關信息全部都保存在一個指定的表里面
. G9 S- j2 E- }( T
CREATE PROCEDURE get_tableinfo AS0 ~: d% ^: x( @: `& ~2 n) R% }3 y7 y
if not exists (select * from dbo.sysobjects where id =object_id(N\'[dbo].[tablespaceinfo]\') and OBJECTPROPERTY(id,N\'IsUserTable\') = 1)6 r5 P: p0 T) z6 ]1 e2 ~
create table tablespaceinfo --創建結果存儲表1 m- M6 q6 V" v( t8 ~/ p
(nameinfo varchar(50) ,
rowsinfo int , reserved varchar(20) ,# q6 R; u6 X\' M2 p0 F+ o
datainfo varchar(20) ,9 {. `# X" Q- C* }( e
index_size varchar(20) ,
unused varchar(20) )
+ }& f3 b6 S8 i
delete from tablespaceinfo --清空數據表: {* Q5 K1 U; h) i* H6 Z: n9 l
declare @tablename varchar(255) --表名稱
declare @cmdsql varchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name$ g9 c; ~) Q/ |" v
from dbo.sysobjects o where OBJECTPROPERTY(o.id, N\'IsTable\') = 1
and o.name not like N\'#%%\' order by o.name
, A; F1 u+ @$ Y# n! {+ ] a/ i/ w
OPEN Info_cursor
k3 @/ H8 C f. m) U
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN; a$ p* @) ]$ Z$ \\6 N3 _, O
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N\'IsUserTable\') = 1)+ x0 P) `& A8 F. f
execute sp_executesql+ S% ?9 T\' Z" _, C
N\'insert into tablespaceinfo exec sp_spaceused @tbname\',9 p h4 n) g0 L% i: ?
N\'@tbname varchar(255)\',) n( _! ?5 g% }4 b0 }6 n, c
@tbname = @tablename/ V$ b3 f0 q. i3 I/ ~
* @" Q# O4 o0 P6 ]) s" U3 i
FETCH NEXT FROM Info_cursor" z% K3 O3 D$ n) J; A4 k+ p% X
INTO @tablename
END
2 \\: [4 R* b6 P* Q) z$ Q7 B\' K ?9 a2 l
CLOSE Info_cursor
DEALLOCATE Info_cursor P4 U/ H% M7 e1 U8 v* a. B
GO+ x! `/ L+ l4 q7 M9 Q. F4 m9 B
* k* S8 B: \\# R0 n1 H9 E
執行存儲過程
: z$ h1 y8 |2 l, q( v& y( ~/ M$ y
exec get_tableinfo
* A, h- `1 K/ N/ i; \\ U, {
查詢運行該存儲過程后得到的結果
\' @, R3 b6 M8 |% o8 @
select *
from tablespaceinfo: p+ S, ~! T" S8 D
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc* C6 A. s3 a\' r4 I2 }+ X
$ g+ Y; ~4 s* w8 y
