查看SQL Server数据库表、索引视图等占用的空间大小

第一种方法(较简单,看的比较吃力)：

EXEC Sp_msforeachtable "EXEC Sp_spaceused '?'"

第二种方法(较复杂,但看的比较清楚，原作者不详)：

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 

第三种方法：

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/chinalantian/archive/2011/08/18/2144872.html