查看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

转载于:https://www.cnblogs.com/linuxredhat/archive/2012/02/06/2339897.html