一个查询数据库中所有表的空间占用情况的存储过程

不知道是否还有更好的获取方法。

 1 CREATE PROCEDURE L_spTableSpaceUsed
 2  AS
 3 SET NOCOUNT ON
 4 
 5 DECLARE @Result TABLE
 6 (
 7     TableName VARCHAR(64)
 8     , [Rows] INT
 9     , Reserved VARCHAR(64)
10     , [Data] VARCHAR(64)
11     , Index_Size VARCHAR(64)
12     , Unused VARCHAR(64)
13 )
14 
15 DECLARE @TableName VARCHAR(64)
16 
17 DECLARE curTable CURSOR FAST_FORWARD FOR
18     SELECT [Name] FROM Sys.Tables WHERE [Name] NOT LIKE 'DatabaseSchema%'
19     
20 OPEN curTable
21 FETCH NEXT FROM curTable INTO @TableName
22 WHILE @@Fetch_Status = 0
23 BEGIN
24     INSERT INTO @Result
25     EXEC sp_SpaceUsed @TableName
26     FETCH NEXT FROM curTable INTO @TableName
27 END
28 CLOSE curTable
29 DEALLOCATE curTable
30 
31 SELECT *
32 FROM @Result
33 ORDER BY Cast(Left([Data], Len([Data]) -2) AS INT) DESC
34 
35 SET NOCOUNT OFF

posted on 2011-06-10 15:22  零度的火  阅读(315)  评论(0)  编辑  收藏  举报

导航