查询SQL数据库中表占用资源情况的语句

 

网上看到的查询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