其实只要使用系统内置的存储过程sp_spaceused就可以得到表的相关信息
如:sp_spaceused 'tablename'
以下是为了方便写的一个存储过程,目的是把当前的所有表的相关信息全部都保存在一个指定的表里面
CREATE
PROCEDURE
get_tableinfo
AS
if
not
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[tablespaceinfo]
'
)
and
OBJECTPROPERTY
(id, N
'
IsUserTable
'
)
=
1
)
create
table
tablespaceinfo
--
创建结果存储表
(nameinfo
varchar
(
50
) ,
rowsinfo
int
, reserved
varchar
(
20
) ,
datainfo
varchar
(
20
) ,
index_size
varchar
(
20
) ,
unused
varchar
(
20
) )
delete
from
tablespaceinfo
--
清空数据表
declare
@tablename
varchar
(
255
)
--
表名称
declare
@cmdsql
varchar
(
500
)
DECLARE
Info_cursor
CURSOR
FOR
select
o.name
from
dbo.sysobjects o
where
OBJECTPROPERTY
(o.id, N
'
IsTable
'
)
=
1
and
o.name
not
like
N
'
#%%
'
order
by
o.name
OPEN
Info_cursor
FETCH
NEXT
FROM
Info_cursor
INTO
@tablename
WHILE
@@FETCH_STATUS
=
0
BEGIN
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(
@tablename
)
and
OBJECTPROPERTY
(id, N
'
IsUserTable
'
)
=
1
)
execute
sp_executesql
N
'
insert into tablespaceinfo exec sp_spaceused @tbname
'
,
N
'
@tbname varchar(255)
'
,
@tbname
=
@tablename
FETCH
NEXT
FROM
Info_cursor
INTO
@tablename
END
CLOSE
Info_cursor
DEALLOCATE
Info_cursor
GO
执行存储过程
exec get_tableinfo
查询运行该存储过程后得到的结果
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc