Show database size info across an instance

/*
Script written by Richard Douglas
HTTP://SQL.RichardDouglas.co.uk

Script will insert data about the sizes of all databases on the instance into a predetermined table.
This can be used as a snapshot or made into a job to chart the growth of databases over time.


Please note that OBJECT_SCHEMA_NAME and DATA_COMPRESSION are SQL 2008 features.
If running on SQL Server 2005:
    The condition using OBJECT_SCHEMA_NAME can be removed and replaced with an appropriate JOIN to sys.schemas.
    Comment out the use of DATA_COMPRESSION WHEN creating storage table.
    
The code assumes the existance of the database DBAControl. This can be changed to use TempDB to test the code.
    Remember never run untrusted code on a Production Server, always review and then run on a development instance first.
*/
USE DBAControl
GO


/*
Create a table to store results.
*/IF NOT EXISTS (SELECT 1 
                FROM DBAControl.sys.tables 
                WHERE Name = 'DatabaseGrowth' 
                AND OBJECT_SCHEMA_NAME(object_id) = 'dbo'
                )
BEGIN
    CREATE TABLE DBAControl.[dbo].[DatabaseGrowth]
    (
         [Database_Name] [char](128) NOT NULL
        ,[Database_Size_MB] DECIMAL(15,2) NOT NULL
        ,[Unallocated_Space_MB] DECIMAL(15,2) NOT NULL
        ,[Reserved_MB] DECIMAL(15,2) NOT NULL
        ,[Data_MB] BIGINT NOT NULL
        ,[Index_Size_MB] BIGINT NULL
        ,[Unused_MB] BIGINT NOT NULL
        ,[DateTimeStamp] DATETIME NOT NULL DEFAULT GETDATE()
    ) ON [PRIMARY]
    WITH (DATA_COMPRESSION = PAGE
END

/*
Insert the data into storage table
*/INSERT INTO DBAControl.dbo.DatabaseGrowth
([Database_Name]
,[Database_Size_MB]
,[Unallocated_Space_MB]
,[Reserved_MB]
,[Data_MB]
,[Index_Size_MB]
,[Unused_MB])
EXEC sp_MSforeachdb    
'DECLARE 
    @pages    BIGINT            -- Working variable for size calc.
    ,@dbname SYSNAME
    ,@dbsize BIGINT
    ,@logsize BIGINT
    ,@reservedpages BIGINT
    ,@usedpages BIGINT
    ,@rowCount BIGINT

SET NOCOUNT ON

    SELECT 
         @dbsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN Size ELSE 0 END))
        ,@logsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN Size ELSE 0 END))
        FROM [?].dbo.sysfiles

    SELECT 
         @reservedpages = SUM(a.total_pages)
        ,@usedpages = SUM(a.used_pages)
        ,@pages = SUM(
                CASE
                    /* XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" */                    WHEN it.internal_type IN (202,204,211,212,213,214,215,216) THEN 0
                    WHEN a.type <> 1 THEN a.used_pages
                    WHEN p.index_id < 2 THEN a.data_pages
                    ELSE 0
                END
            )
    FROM [?].sys.partitions p 
    JOIN [?].sys.allocation_units a ON p.partition_id = a.container_id
    LEFT JOIN [?].sys.internal_tables it on p.object_id = it.object_id

    /* unallocated space could not be negative */    SELECT 
         database_name = ''?''
        ,database_size = (@dbsize + @logsize) * 8192 / 1048576
        ,''unallocated space'' = LTRIM(STR((CASE WHEN @dbsize >= @reservedpages THEN (CONVERT (dec (15,2),@dbsize) - CONVERT (DEC (15,2),@reservedpages)) * 8192 / 1048576 ELSE 0 END),15,2))
        ,reserved = LTRIM(STR((@reservedpages * 8192 / 1024.)/1024,15,0))
        ,data = LTRIM(STR((@pages * 8192 / 1024.)/1024,15,0))
        ,index_size = LTRIM(STR(((@usedpages - @pages) * 8192 / 1024.)/1024,15,0))
        ,unused = LTRIM(STR(((@reservedpages - @usedpages) * 8192 / 1024.)/1024,15,0))'

/*Show data*/
SELECT 
Database_Name
,Database_Size_MB
,Unallocated_Space_MB
,Reserved_MB
,Data_MB
,Index_Size_MB
,Unused_MB
,DateTimeStamp
FROM DBAControl.[dbo].[DatabaseGrowth]