获取SQL Server数据库里表的占用容量大小
CREATE PROCEDURE get_tableinfo AS
if not exists (select * from dbosysobjects where id = object_id(N[dbo][tablespaceinfo]) and OBJECTPROPERTY(id NIsUserTable) = )
create table tablespaceinfo 创建结果存储表
(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 tablespaceinfo exec 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
***/
