SQL Server Database File Sizes

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE CP_Server_FileSizing   AS 
DECLARE @dbid INT
DECLARE @MaxId INT
DECLARE @dbName SYSNAME
IF NOT EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME ='SQL_Statistics') 
    BEGIN
        CREATE TABLE MASTER.dbo.SQL_Statistics (
            Svr_Name varchar( 15),
        [DBName] [varchar] (75) NULL ,
        [DB_DevName] [varchar] (75) NULL,
        [DB_Filename] [varchar] (100) NULL ,
        [DB_Size] [int] NULL ,
        [Stat_Time] [datetime] NULL 
        ) 
    END
/** The DB_Size returned is in 8KB Page Blocks. So Multiply
    by 8 to get KB then do the rest of the math againt 1024 **/SET @MaxId = (SELECT MAX(dbid) FROM MASTER.dbo.sysdatabases)
SET @dbid = 1 
WHILE @dbid <= @MaxId 
BEGIN
SET @dbName = (SELECT name FROM MASTER.dbo.sysdatabases WHERE dbid = @dbid)
IF (@dbname IS NOT NULL) 
BEGIN
EXEC ('SET QUOTED_IDENTIFIER OFF
insert into SQL_Statistics SELECT "' + @@ServerName +'" AS SVR_Name,
    "' + @dbname +'" AS DB_Name, 
RTRIM(name) AS DB_DevName, 
RTRIM(filename) AS DB_Filename, 
RTRIM(size) AS DBSize, CURRENT_TIMESTAMP as Stat_Time FROM [' + @dbname + '].dbo.sysfiles')
SET @dbid = @dbid + 1
END
ELSE
SET @dbid = @dbid + 1
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO