MSSQL DB 사이즈 보기 (DBA 관리용 시스템 프로시저)

/************************************************************************************************

-- sp_spaceused0

-- 서버의 전체 DB에 대한 사이즈를 출력한다.

************************************************************************************************/

USE MASTER

GO

IF OBJECT_ID('sp_spaceused0') IS NOT NULL

   DROP PROCEDURE sp_spaceused0

GO

CREATE PROC sp_spaceused0

AS

  SET NOCOUNT ON

  DECLARE @dbname SYSNAME

  DECLARE @sql VARCHAR(1000)

  DECLARE @pages INT  

  DECLARE @dbsize DEC(15,0)  

  DECLARE @logsize DEC(15)  

  DECLARE @str VARCHAR(2000)

  CREATE TABLE #spt_space  

  ( dbname SYSNAME NOT NULL

  , pages INT NULL

  , dbsize DEC(15,0) NULL

  , logsize DEC(15,0) NULL

  , bytesperpage DEC(15,0) NULL

  , pagesperMB  DEC(15,0) NULL

  , reserved DEC(15) NULL 

  , data DEC(15) NULL

  , indexp DEC(15) NULL

  , unused DEC(15) NULL 

  )  

  INSERT INTO #spt_space(dbname)

  SELECT name

  FROM master.dbo.sysdatabases

  WHERE name not in ('master','msdb','model','tempdb','pubs','northwind')

  

  DECLARE cur_db_size CURSOR READ_ONLY

  FOR 

    SELECT dbname

    FROM #spt_space

  OPEN cur_db_size

  FETCH NEXT FROM cur_db_size

  INTO @dbname

  WHILE @@FETCH_status = 0

  BEGIN

  

    SET @str = ''

    SET @str = @str + 'UPDATE #spt_space '

    SET @str = @str + 'SET dbsize = (SELECT SUM(CONVERT(DEC(15),size)) '

    SET @str = @str + '              FROM ' + @dbname + '.dbo.sysfiles '

    SET @str = @str + '              WHERE (status & 64 = 0) '

    SET @str = @str + '             ) '

    SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''

  

    EXEC(@str)

    SET @str = ''

    SET @str = @str + 'UPDATE #spt_space '

    SET @str = @str + 'SET logsize = (SELECT SUM(CONVERT(DEC(15),size)) '

    SET @str = @str + '               FROM ' + @dbname + '.dbo.sysfiles '

    SET @str = @str + '               WHERE (status & 64 <> 0) '

    SET @str = @str + '              ) '

    SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''

    EXEC(@str)

    SET @str = ''

    SET @str = @str + 'UPDATE #spt_space '

    SET @str = @str + 'SET bytesperpage = (SELECT low '

    SET @str = @str + '                    FROM master.dbo.spt_values '

    SET @str = @str + '                    WHERE number = 1 AND type = ''E'' '  

    SET @str = @str + '                   ) '

    SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''

 

    EXEC(@str)

    SET @str = ''

    SET @str = @str + 'UPDATE #spt_space '

    SET @str = @str + 'SET pagesperMB = 1048576 / (SELECT bytesperpage '

    SET @str = @str + '                            FROM #spt_space '

    SET @str = @str + '                            WHERE dbname = ''' + @dbname + ''''

    SET @str = @str + '                           ) ' 

    SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''

    EXEC(@str)

    SET @str = ''

    SET @str = @str + 'UPDATE #spt_space '

    SET @str = @str + 'SET reserved = (SELECT SUM(CONVERT(DEC(15),reserved)) '

    SET @str = @str + '                FROM ' + @dbname + '.dbo.sysindexes '

    SET @str = @str + '                WHERE indid in (0, 1, 255) '

    SET @str = @str + '               ) '

    SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''

    EXEC(@str)

    -- data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)  

    SET @str = ''

    SET @str = @str + 'UPDATE #spt_space '

    SET @str = @str + 'SET pages = (SELECT SUM(CONVERT(DEC(15),dpages)) '

    SET @str = @str + '             FROM ' + @dbname + '.dbo.sysindexes '

    SET @str = @str + '             WHERE indid < 2 '

    SET @str = @str + '            ) '

    SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''

    EXEC(@str)

    SET @str = ''

    SET @str = @str + 'UPDATE #spt_space '

    SET @str = @str + 'SET pages = pages + (SELECT + ISNULL(SUM(CONVERT(DEC(15),used)), 0) ' 

    SET @str = @str + '                     FROM ' + @dbname + '.dbo.sysindexes '

    SET @str = @str + '                     WHERE indid = 255 '

    SET @str = @str + '                    ) '

    SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''

    EXEC(@str)

    SET @str = ''

    SET @str = @str + 'UPDATE #spt_space '

    SET @str = @str + 'SET data = (SELECT pages '

    SET @str = @str + '            FROM #spt_space '

    SET @str = @str + '            WHERE dbname = ''' + @dbname + ''''

    SET @str = @str + '           ) '

    SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''

    EXEC(@str)

    -- index: sum(used) where indid in (0, 1, 255) - @data 

    SET @str = ''

    SET @str = @str + 'UPDATE #spt_space '

    SET @str = @str + 'SET indexp = (SELECT SUM(CONVERT(DEC(15),used)) '

    SET @str = @str + '              FROM ' + @dbname + '.dbo.sysindexes '

    SET @str = @str + '              WHERE indid IN (0, 1, 255) '

    SET @str = @str + '             ) - data '

    SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''

    EXEC(@str) 

    -- unused: sum(reserved) - sum(used) where indid in (0, 1, 255) 

    SET @str = ''

    SET @str = @str + 'UPDATE #spt_space '

    SET @str = @str + 'SET unused = reserved - (SELECT SUM(CONVERT(DEC(15),used)) ' 

    SET @str = @str + '                         FROM ' + @dbname + '.dbo.sysindexes '

    SET @str = @str + '                         WHERE indid IN (0, 1, 255)) '

    SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''

    EXEC(@str)

    FETCH NEXT FROM cur_db_size

    INTO @dbname

  END

  CLOSE cur_db_size

  DEALLOCATE cur_db_size

  SELECT

      dbname AS "dBName"   

  ,   CONVERT(DEC(10,2),(dbsize + logsize) / pagesperMB) AS "DiskSize(MB)"

  ,   CONVERT(DEC(10,2),(reserved * bytesperpage / 1024) / 1000) AS "ReservedSize(MB)"

  ,   CONVERT(DEC(10,2),(data * bytesperpage / 1024) / 1000) AS "DataSize(MB)"

  ,   CONVERT(DEC(10,2),(indexp * bytesperpage / 1024) / 1000) AS "IndexSize(MB)"

  ,   CONVERT(DEC(10,2),(unused * bytesperpage / 1024) / 1000) AS "UnusedSize(MB)"

  FROM #spt_space

  DROP TABLE #spt_space;

  RETURN(0) --sp_spaceused0

  SET NOCOUNT OFF

GO

  

GRANT EXECUTE ON sp_spaceused0 TO PUBLIC

GO