If you want to do the same thing in each database, then this would be another alternative solution
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)
)
--SELECT @SQL
EXECUTE sp_executesql @SQL
Just remove the space between the x and the 0 in the following string in the script '&#x 0D;'
The above example loops through all the databaes and gets their file usage, free space, used space, total space etc, so I can track DB growth.