统计SQL SERVER数据库内所有表占用空间并排序

以下存储过程能够统计sqlserver数据库内所有用户表所占用的空间,并按照占用空间大小倒序排列。

--------------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE GetAllTableSizes

AS

DECLARE @TableName VARCHAR(100)

DECLARE tableCursor CURSOR FORWARD_ONLY

FOR

select [name] from dbo.sysobjects

  where OBJECTPROPERTY(id, N'IsUserTable') = 1

  FOR READ ONLY

 

CREATE TABLE #TempTable

(

  tableName varchar(100),

  numberofRows varchar(100),

  reservedSize varchar(50),

  dataSize varchar(50),

  indexSize varchar(50),

  unusedSize varchar(50)

)

OPEN tableCursor

WHILE (1=1)

BEGIN

  FETCH NEXT FROM tableCursor INTO @TableName

  IF(@@FETCH_STATUS<>0) BREAK;

  INSERT #TempTable  EXEC sp_spaceused @TableName

END

CLOSE tableCursor

DEALLOCATE tableCursor

 

UPDATE #TempTable

SET reservedSize = REPLACE(reservedSize, ' KB', '')

 

SELECT tableName 'Table Name',

  numberofRows 'Total Rows',

  reservedSize 'Reserved KB',

  dataSize 'Data Size',

  indexSize 'Index Size',

  unusedSize 'Unused Size'

FROM #TempTable

ORDER BY CONVERT(bigint,reservedSize) DESC

 

DROP TABLE #TempTable

go

--------------------------------------------------------------------------------------------------------------------------------

  • 本文链接地址:http://www.dbainfo.net/sql-server-database-all-tables-space-order-desc.htm
  • 本文为dbainfo个人原创,请在尊重作者劳动成果的前提下进行转载;
  • 转载务必注明原始出处 : Sybase数据库技术,数据库恢复专家
  • 对《统计SQL SERVER数据库内所有表占用空间并排序》有何疑问或见解,请在本文下方发表;
  • 对网站还有其他问题或建议,请提交在留言板,谢谢!
  • 目前还没有任何评论.
    :wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :?: :-| :-x :-o :-P :-D :-? :) :( :!: 8-O 8)