SQL: Creating a Storage Report of Databases Residing within Server

-- Get sizes of all databases from system (not real-time)
WITH fs
AS
(
    select database_id, type, size * 8.0/1048576 size
    from sys.master_files
)
SELECT 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeGB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeGB
ORDER BY DataFileSizeGB Desc    
FROM sys.databases db

-- Get sizes of all databases (no logs)
SELECT d.NAME
    ,(SUM(CAST(db.size AS bigint)) * 8 / 1048576, 2) Size_GBs
FROM sys.master_files db
INNER JOIN sys.databases d ON d.database_id = db.database_id
WHERE d.database_id > 4 -- omit system databases
GROUP BY d.NAME
ORDER BY d.NAME

-- Get sizes of all databases with logs aggregated
SET ANSI_WARNINGS OFF
GO

IF OBJECT_ID('tempdb.dbo.#storageReport') IS NOT NULL
    DROP TABLE #storageReport

CREATE TABLE #storageReport (
      database_name VARCHAR(255) PRIMARY KEY
    , log_size_gb DECIMAL(5,2)
    , db_size_gb DECIMAL(5,2)
    , total_size_gb DECIMAL(5,2)
)

DECLARE @command varchar(max) 
SELECT @command = '
	IF "?" NOT IN ("master","tempdb", "model","msdb","monitoring","ReportServer","ReportServerTempDB") BEGIN
	USE ?
    INSERT INTO #storageReport (database_name, log_size_gb, db_size_gb, total_size_gb)
	SELECT 
      database_name = DB_NAME(database_id)
    , log_size_gb = CAST(((SUM(CASE WHEN type_desc = "LOG" THEN size END)*8.00) / 1048576) AS DECIMAL(5,2))
    , db_size_gb = CAST(((SUM(CASE WHEN type_desc = "ROWS" THEN size END)*8.00) / 1048576) AS DECIMAL(5,2))
    , total_size_gb = CAST(SUM(size)*8.00/1048576 AS DECIMAL(5,2))
	FROM sys.master_files WITH(NOWAIT)
	WHERE database_id = DB_ID()
	GROUP BY database_id
	END
	'
EXEC sp_MSforeachdb @command 
SELECT * FROM #storageReport

EXEC sp_MSforeachdb
	'USE ? SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
	FROM sys.master_files WITH(NOWAIT)
	WHERE database_id = DB_ID() -- for current db 
	GROUP BY database_id
	'

Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'USE ? SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size EN' is too long. Maximum length is 128.

This happens due to the fact that single quotes are being used to encapsulate the command as well as declaring a string.
    
DECLARE @command varchar(9000) 
SELECT @command = "USE ? SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
	FROM sys.master_files WITH(NOWAIT)
	WHERE database_id = DB_ID() -- for current db 
	GROUP BY database_id
	" 
EXEC sp_MSforeachdb @command     

Msg 131, Level 15, State 3, Line 1
The size (9000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).
Msg 103, Level 15, State 4, Line 2
The identifier that starts with 'USE ? SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size EN' is too long. Maximum length is 128.
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@command".
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@command".

a. Command declaration as varchar has a max value of 8000 - remember that. Perhaps, one could statically imprint this NVARCHAR(MAX) to declare maximum allowable characters in a command.
b. It's illegal to use variables inside a sql string and use them in exec statements. the variables would be out of scope. In T-SQL the single quotes are treated as variables. Hence, the workaround is to use double quotes to specify string values and single quotes to encapsulate the whole command.

Error:
-----------
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 8152, Level 16, State 2, Line 4
String or binary data would be truncated.
The statement has been terminated.

Resolution:
-----------
The SUM or aggregate function may encounter data point values of NULL.
When adding a INT/DOUBLE value with NULL, SQL would throw out warnings.
Hence, this can safely be ignored by adding the 'SET ANSI_WARNINGS OFF' declaration
The other error tells us that a string is being truncated. That's a hint for us to increase the VARCHAR length of a string datatype declaration