查看数据库中表占用空间
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