其实只要使用系统内置的存储过程sp_spaceused就可以得到表的相关信息
如sp_spaceused tablename
以下是为了方便写的一个存储过程目的是把当前的所有表的相关信息全部都保存在一个指定的表里面
CREATE PROCEDURE get_tableinfo AS
if not exists (select * from dbosysobjects where id = object_id(N[dbo][tablespaceinfo]) and OBJECTPROPERTY(id NIsUserTable) = )
create tabletablespaceinfo 创建结果存储表
(nameinfo varchar()
rowsinfo int reserved varchar()
datainfo varchar()
index_size varchar()
unused varchar() )
delete from tablespaceinfo 清空数据表
declare @tablename varchar()表名称
declare @cmdsql varchar()
DECLARE Info_cursor CURSOR FOR
select oname
from dbosysobjects o where OBJECTPROPERTY(oid NIsTable) =
and oname not like N#%%order by oname
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS =
BEGIN
if exists (select * from dbosysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id NIsUserTable) = )
execute sp_executesql
Ninsert into tablespaceinfoexec sp_spaceused @tbname
N@tbname varchar()
@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)))) as int) desc
