查看数据库中表占用空间 

 1 DECLARE @Data TABLE (
 2     name       VARCHAR(100),
 3     row        VARCHAR(100),
 4     reserved   VARCHAR(100),
 5     data       VARCHAR(100),
 6     index_size VARCHAR(100),
 7     unused     VARCHAR(100)
 8 )
 9 
10 DECLARE @name VARCHAR(100)
11 DECLARE cur CURSOR FOR
12 SELECT name
13 FROM sysobjects
14 WHERE xtype = 'u'
15 ORDER BY name
16 OPEN cur
17 FETCH NEXT FROM cur
18 INTO @name
19 WHILE @@fetch_status = 0
20 BEGIN
21     INSERT INTO @Data
22     EXEC sp_spaceused @name
23     PRINT @name
24 
25     FETCH NEXT FROM cur
26     INTO @name
27 END
28 CLOSE cur
29 DEALLOCATE cur
30 
31 DECLARE @DataNew TABLE (
32     name       VARCHAR(100),
33     row        INT,
34     reserved   INT,
35     data       INT,
36     index_size INT,
37     unused     INT
38 )
39 
40 INSERT INTO @DataNew
41 SELECT name,
42        CONVERT(INT, row) AS row,
43        CONVERT(INT, REPLACE(reserved, 'KB', '')) AS reserved,
44        CONVERT(INT, REPLACE(data, 'KB', '')) AS data,
45        CONVERT(INT, REPLACE(index_size, 'KB', '')) AS index_size,
46        CONVERT(INT, REPLACE(unused, 'KB', '')) AS unused
47 FROM @Data
48 
49 SELECT *
50 FROM @DataNew
51 ORDER BY data DESC

 

posted on 2018-01-22 11:44  木龙哥  阅读(161)  评论(0)    收藏  举报

导航