create table dbsize (
  Dbname sysname,
  dbstatus varchar(50),
  Recovery_Model varchar(40),
  file_Size_MB decimal(30,2),
  Space_Used_MB decimal(30,2),
  Free_Space_MB decimal(30,2),
  entry_date datetime);

INSERT INTO dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB, entry_date)
  SELECT Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB, GetDate()
    FROM #dbsize;

CREATE TABLE [DatabaseFileUsage]
(
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [sysname] NOT NULL,
	[FileID] [int] NULL,
	[FileSizeMB] [decimal](18, 2) NULL,
	[SpaceUsedMB] [decimal](18, 2) NULL,
	[FreeSpaceMB] [decimal](18, 2) NULL,
	[LogicalName] [sysname] NOT NULL,
	[FileLocation] [sysname] NOT NULL,
	[DateCollected] [date] NULL DEFAULT GETUTCDATE(),
	CONSTRAINT [PK_DB_DatabaseFileUsage] PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = REPLACE(
					CAST(
							(
								SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
								'INSERT INTO [DatabaseFileUsage] (DatabaseName, FileID, FileSizeMB, SpaceUsedMB, FreeSpaceMB, LogicalName, FileLocation)' + CHAR(13) + CHAR(10) + 
								'SELECT ' + CHAR(13) + CHAR(10) +
								'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
								'a.FILEID, ' + CHAR(13) + CHAR(10) +
								'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
								'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
								'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
								'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
								'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
								FROM sys.databases
								FOR XML PATH('')
							) AS NVARCHAR(MAX)
						),'&#x 0D;',CHAR(13) + CHAR(10) --remove the space between '&#x 0D;'
)
EXECUTE sp_executesql @SQL