SQL Server - Script to show the size of all databases and database files

As a practise to write T-SQL code, comes up with the following script:

-- show size of all databases and database files
-- uncommenet out the following 4 lines if used in  sqlcmd

:setvar sqlcmdheaders            40     -- "page size"
:setvar sqlcmdcolwidth          132     -- line width
:setvar sqlcmdmaxfixedtypewidth  32     -- max column width (fixed length)
:setvar sqlcmdmaxvartypewidth    32     -- max column width (varying length)




IF Object_id(N'tempdb..#tempTbl') IS NOT NULL
DROP TABLE #temptbl


CREATE TABLE #tempTbl
( DBName VARCHAR(50),
  DBFileName VARCHAR(50),
  PhysicalFileName NVARCHAR(260),
  FileSizeMB decimal(18,1)
)



declare @cmd1 varchar(500)
set @cmd1 ='
insert into #tempTbl
SELECT ''?'' as DBName
   ,cast(f.name as varchar(25)) as DBFileName
   ,f.Filename as PhysicalFileName
   ,cast (round((f.size*8)/1024.0,2) as decimal(18,2)) as FileSizeinMB
FROM ?..SYSFILES f
'
exec sp_MSforeachdb @command1=@cmd1


select *
from #tempTbl
order by DBName

select case when (GROUPING(DBName)=1) then '*** Total size ***'
       else isnull(DBname, 'UNKNOWN')
       end AS DBName
      ,SUM(FileSizeMB) As FileSizeMBSum
from #tempTbl
group by DBName with ROLLUP
order by FileSizeMBSum

Sample output when used in sqlcmd:

$ sqlcmd -i dbfile_size.sql
DBName                           DBFileName                       PhysicalFileName                 FileSizeMB
-------------------------------- -------------------------------- -------------------------------- --------------------
DBAdmin                          DBAdmin                          c:\Program Files\Microsoft SQL S                  4.0
DBAdmin                          DBAdmin_log                      c:\Program Files\Microsoft SQL S                  1.0
DBAdmin                          DBAdmin_P01                      c:\Program Files\Microsoft SQL S                  4.0
master                           master                           c:\Program Files\Microsoft SQL S                  4.0
master                           mastlog                          c:\Program Files\Microsoft SQL S                   .8
model                            modeldev                         c:\Program Files\Microsoft SQL S                  3.1
model                            modellog                         c:\Program Files\Microsoft SQL S                   .8
msdb                             MSDBData                         c:\Program Files\Microsoft SQL S                 13.8
msdb                             MSDBLog                          c:\Program Files\Microsoft SQL S                   .8
ReportServer$SQLEXPRESS          ReportServer$SQLEXPRESS          c:\Program Files\Microsoft SQL S                  5.1
ReportServer$SQLEXPRESS          ReportServer$SQLEXPRESS_l        c:\Program Files\Microsoft SQL S                  4.2
ReportServer$SQLEXPRESSTempDB    ReportServer$SQLEXPRESSTe        c:\Program Files\Microsoft SQL S                  4.1
ReportServer$SQLEXPRESSTempDB    ReportServer$SQLEXPRESSTe        c:\Program Files\Microsoft SQL S                  1.1
tempdb                           tempdev                          c:\Program Files\Microsoft SQL S                  3.1
tempdb                           templog                          c:\Program Files\Microsoft SQL S                   .8

(15 rows affected)
DBName                           FileSizeMBSum
-------------------------------- ----------------------------------------
model                                                                 3.9
tempdb                                                                3.9
master                                                                4.8
ReportServer$SQLEXPRESSTempDB                                         5.2
DBAdmin                                                               9.0
ReportServer$SQLEXPRESS                                               9.3
msdb                                                                 14.6
*** Total size ***                                                   50.7

(8 rows affected)