查看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)
    )
--by www.jb200.com
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

--by www.jb200.com
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