共1 条记录, 每页显示 10 条, 页签:
[1]
[浏览完整版]
标题：获取SQL Server数据库里表占用容量大小
1楼
wangxinxin 发表于：2010-12-13 13:02:13其实只要使用系统内置的存储过程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
$ 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
共1 条记录, 每页显示 10 条, 页签:
[1]