查看SQL Server数据库表、索引视图等占用的空间大小
第一种方法(较简单,看的比较吃力)：
|
1 |
EXEC Sp_msforeachtable "EXEC Sp_spaceused '?'" |
第二种方法(较复杂,但看的比较清楚，原作者不详)：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55 |
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) ) DELETE FROM tablespaceinfo --清空数据表 DECLARE @tablename VARCHAR(255) --表名称 DECLARE @cmdsql VARCHAR(500) 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 OPEN Info_cursor FETCH NEXT FROM Info_cursor INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN 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', N'@tbname varchar(255)', @tbname = @tablename FETCH NEXT FROM Info_cursor INTO @tablename END CLOSE Info_cursor DEALLOCATE Info_cursor GO --itlearner注：显示数据库信息 Sp_spaceused @updateusage = 'TRUE' --itlearner注：显示表信息 SELECT * FROM tablespaceinfo ORDER BY Cast(LEFT(Ltrim(Rtrim(reserved)), Len(Ltrim(Rtrim(reserved))) - 2) AS INT) DESC |
第三种方法：
|
1
2
3
4
5
6
7
8
9
10 |
SELECT Object_name(id) tablename, 8 * reserved / 1024 reserved_, Rtrim(8 * dpages / 1024) + 'Mb' used, 8 * ( reserved - dpages ) / 1024 unused, 8 * dpages / 1024 - rows / 1024 * minlen / 1024 free, rows, * FROM sysindexes WHERE indid = 1 ORDER BY reserved_ DESC |
参考：http://msdn.microsoft.com/zh-cn/library/ms188776.aspx
