select server_name, database_name, backup_start_date, backup_size
from msdb.dbo.backupset 
where type = 'D' 
and database_name = 'dbname'
order by backup_start_date desc

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,
 CONSTRAINT [PK_DatabaseFileUsage] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)
)
GO
CREATE PROCEDURE [InsertDatabaseFileUsage]
AS
BEGIN
	DECLARE @SQL NVARCHAR(MAX)
		SELECT @SQL = REPLACE(
						CAST(
								(
									SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
									'INSERT INTO [#####].[dbo].[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 ON THIS LINE BETWEEN the x and the 0
					)
	--SELECT @SQL
	EXECUTE sp_executesql @SQL
END