统计MS SQL 数据库各表数据量

--用于统计MS SQL 数据库各表数据量
DECLARE @id INT
DECLARE @type CHARACTER(2)
DECLARE @pages INT
DECLARE @dbname SYSNAME
DECLARE @dbsize DEC(15, 0)
DECLARE @bytesperpage DEC(15, 0)
DECLARE @pagesperMB DEC(15, 0)

CREATE TABLE #spt_space
  (
     objid    INT NULL,
     rows     INT NULL,
     reserved DEC(15) NULL,
     data     DEC(15) NULL,
     indexp   DEC(15) NULL,
     unused   DEC(15) NULL
  )

SET nocount ON

-- Create a cursor to loop through the user   tables 
DECLARE c_tables CURSOR FOR
  SELECT id
  FROM   sysobjects
  WHERE  xtype = 'U'

OPEN c_tables

FETCH next FROM c_tables INTO @id

WHILE @@FETCH_STATUS = 0
  BEGIN
      /* Code from sp_spaceused */
      INSERT INTO #spt_space
                  (objid,
                   reserved)
      SELECT objid = @id,
             Sum(reserved)
      FROM   sysindexes
      WHERE  indid IN ( 0, 1, 255 )
             AND id = @id

      SELECT @pages = Sum(dpages)
      FROM   sysindexes
      WHERE  indid < 2
             AND id = @id

      SELECT @pages = @pages + Isnull(Sum(used), 0)
      FROM   sysindexes
      WHERE  indid = 255
             AND id = @id

      UPDATE #spt_space
      SET    data = @pages
      WHERE  objid = @id

      /* index: sum(used) where indid in (0, 1, 255) - data */
      UPDATE #spt_space
      SET    indexp = (SELECT Sum(used)
                       FROM   sysindexes
                       WHERE  indid IN ( 0, 1, 255 )
                              AND id = @id) - data
      WHERE  objid = @id

      /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
      UPDATE #spt_space
      SET    unused = reserved - (SELECT Sum(used)
                                  FROM   sysindexes
                                  WHERE  indid IN ( 0, 1, 255 )
                                         AND id = @id)
      WHERE  objid = @id

      UPDATE #spt_space
      SET    rows = i.rows
      FROM   sysindexes i
      WHERE  i.indid < 2
             AND i.id = @id
             AND objid = @id

      FETCH next FROM c_tables INTO @id
  END

SELECT TableName   = (SELECT LEFT(name, 60)
                    FROM   sysobjects
                    WHERE  id = objid),
       Rows        = CONVERT(CHAR(11), rows),
       ReservedKB  = Ltrim(Str(reserved * d.low / 1024., 15, 0) + ' '
                          + 'KB'),
       DataKB      = Ltrim(Str(data * d.low / 1024., 15, 0) + ' ' + 'KB'),
       IndexSizeKB = Ltrim(Str(indexp * d.low / 1024., 15, 0) + ' ' + 'KB'),
       UnusedKB    = Ltrim(Str(unused * d.low / 1024., 15, 0) + ' ' + 'KB')
FROM   #spt_space,
       master.dbo.spt_values d
WHERE  d.number = 1
       AND d.type = 'E'
ORDER  BY reserved DESC

DROP TABLE #spt_space

CLOSE c_tables

DEALLOCATE c_tables