EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

CREATE TABLE #EXECPLAN(database_name NVARCHAR(1000),database_size nvarchar(100),unallocated_space nvarchar(100),reserved nvarchar(100),data nvarchar(100),index_size nvarchar(100),unused nvarchar(100))
	INSERT INTO #EXECPLAN
	EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'
	SELECT * FROM #EXECPLAN

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = 
	REPLACE(
		CAST(
			(
				SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
				--PUT WHAT YOU WANT TO DO IN EACH DATABASE IN THIS BLOCK
				----
					'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 BEFORE RUNNING
	)
	--SELECT @SQL
EXECUTE sp_executesql @SQL

IF OBJECT_ID ('tempdb..##DatabaseFileDetails') IS NOT NULL
BEGIN
	DROP TABLE ##DatabaseFileDetails;
END
GO

EXECUTE sp_msforeachdb N'USE [?]
						
						IF OBJECT_ID (''tempdb..##DatabaseFileDetails'') IS NULL
						BEGIN
							SELECT	DB_NAME() AS DatabaseName,
									[file_id] AS FileId,
									[name] AS [FileName],
									[type_desc] AS FileType,
									[state_desc] AS FileState,
									CAST((([size]*8/1024.0)/1024.0) AS DECIMAL(18,2)) AS FileSizeGB,
									CAST((((FILEPROPERTY([name],''spaceused'')*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS UsedSpaceGB,
									CAST(((((size - FILEPROPERTY([name],''spaceused''))*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS FreeSpaceGB,
									--[size] AS PagesAllocated,
									--FILEPROPERTY([name],''spaceused'') AS UsedPages,
									--(size)- (FILEPROPERTY([name],''spaceused'')) AS FreePages,
									[physical_name] AS FilePath
							INTO	##DatabaseFileDetails
							FROM	sys.database_files
						END
						ELSE
							INSERT INTO ##DatabaseFileDetails
							SELECT	DB_NAME() AS DatabaseName,
									[file_id] AS FileId,
									[name] AS [FileName],
									[type_desc] AS FileType,
									[state_desc] AS FileState,
									CAST((([size]*8/1024.0)/1024.0) AS DECIMAL(18,2)) AS FileSizeGB,
									CAST((((FILEPROPERTY([name],''spaceused'')*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS UsedSpaceGB,
									CAST(((((size - FILEPROPERTY([name],''spaceused''))*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS FreeSpaceGB,
									--[size] AS PagesAllocated,
									--FILEPROPERTY([name],''spaceused'') AS UsedPages,
									--(size)- (FILEPROPERTY([name],''spaceused'')) AS FreePages,
									[physical_name] AS FilePath
							FROM	sys.database_files'
GO

SELECT	* 
FROM	##DatabaseFileDetails
ORDER BY DatabaseName