tests/Test-SqlDatabaseFileInfo.ps1

function Test-SqlDatabaseFileInfo {
    [CmdletBinding()]
    param (
        [parameter()][string] $TestName = "SQL Instance Database File Info",
        [parameter()][string] $TestGroup = "operation",
        [parameter()][string] $TestCategory = "SQL",
        [parameter()][string] $Description = "Database and Log file sizes and growth settings",
        [parameter()][hashtable] $ScriptParams
    )
    try {
        $startTime = (Get-Date)
        #[int]$Setting = Get-CmHealthDefaultValue -KeySet "keygroup:keyname" -DataSet $CmHealthConfig
        [System.Collections.Generic.List[PSObject]]$tempdata = @() # for detailed test output to return if needed
        $stat   = "PASS" # do not change this
        $except = "WARNING" # or "FAIL"
        $msg    = "No issues found" # do not change this either
        $query = "CREATE TABLE ##temp_DatabaseAnalysis
(DatabaseName sysname, Name sysname, physical_name NVARCHAR(500), size DECIMAL (18,2), FreeSpace DECIMAL (18,2) )
EXEC sp_msforeachdb '
USE [?];
INSERT INTO ##temp_DatabaseAnalysis (DatabaseName, Name, physical_name, Size, FreeSpace)
    SELECT DB_NAME() AS [DatabaseName], Name, physical_name,
    CAST(CAST(ROUND(CAST(size as decimal) * 8.0/1024.0,2) as DECIMAL(18,2)) AS NVARCHAR) Size,
    CAST(CAST(ROUND(CAST(size as decimal) * 8.0/1024.0,2) as DECIMAL(18,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as DECIMAL(18,2)) as NVARCHAR) As FreeSpace
    FROM sys.database_files
'
SELECT db.name, db.recovery_model_desc,
CASE
    WHEN mf.type_desc = 'ROWS' THEN 'Database'
    ELSE 'Logs'
END AS type_desc, mf.physical_name,
(mf.size*8)/1024 as Size,
CASE
    WHEN mf.max_size = -1 THEN 'Unlimited'
    ELSE CAST(mf.max_size as VARCHAR(200))
END AS Max_Size,
tmp.FreeSpace,
CASE
    WHEN mf.is_percent_growth = 1 THEN CAST(mf.growth as VARCHAR(200)) + '%'
    ELSE CAST(mf.growth as VARCHAR(200)) + ' MB'
END AS Growth,
(SELECT COUNT(1) FROM sys.master_files mf1 WHERE mf1.type_desc = 'ROWS' AND db.database_id = mf1.database_id ) AS CountDataFile,
(SELECT COUNT(1) FROM sys.master_files mf1 WHERE mf1.type_desc = 'LOG' AND db.database_id = mf1.database_id ) AS CountLogFile
FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id
INNER JOIN ##temp_DatabaseAnalysis tmp ON mf.physical_name = tmp.physical_name
DROP TABLE ##temp_DatabaseAnalysis"

        $res = Get-CmSqlQueryResult -Query $query -Params $ScriptParams
        $res | Foreach-Object {
            $tempdata.Add(
                [PSCustomObject]@{
                    Name         = $_.Name
                    RecoverModel = $_.recovery_model_desc
                    Type         = $_.type_desc
                    FilePath     = $_.physical_name
                    SizeMB       = $_.Size
                    MaxSizeMB    = $_.Max_Size
                    FreeSpaceMB  = $_.FreeSpace
                    Growth       = $_.Growth
                    DBFiles      = $_.CountDataFile
                    LogFiles     = $_.CountLogFile
                }
            )
        }
        if ($res.Count -gt 0) {
            $stat = $except
            $msg  = "$($res.Count) items found"
            #$res | Foreach-Object {$tempdata.Add( [pscustomobject]@{Name=$_.Name} )}
        }
    }
    catch {
        $stat = 'ERROR'
        $msg = $_.Exception.Message -join ';'
    }
    finally {
        Set-CmhOutputData
    }
}