获取SQL Server数据库里表的占用容量大小代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】获取SQL Server数据库里表的占用容量大小

作者:Dezai.CN / 发布于2012/1/11/ 734

<div>CREATE PROCEDURE get_tableinfo AS</div>
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) )</div>
<div>
delete from tablespaceinfo --清空数据表</div>
<div>declare @tablename varchar(255) --表名称</div>
<div>declare @cmdsql varchar(500)</div>
<div>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</div>
<div>OPEN Info_cursor</div>
<div>FETCH NEXT FROM Info_cursor 
INTO @tablename</div>
<div>WHILE @@FETCH_STATUS = 0 
BEGIN</div>
<div> 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', 
 <a href="mailto:N'@tbname">N'@tbname</a> varchar(255)', 
 @tbname = @tablename</div>
<div> FETCH NEXT FROM Info_cursor 
 INTO @tablename 
END</div>
<div>CLOSE Info_cursor 
DEALLOCATE Info_cursor 
GO</div>
<div>
</div>
<div>执行存储过程 
exec get_tableinfo</div>
<div>查询运行该存储过程后得到的结果 
select * 
from tablespaceinfo 
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc</div>
<div></div>

试试其它关键字

　表的占用容量大小　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)