How fetch sysaltfiles information using Powershell

I recently have developed an affinity for using Powershell. I saw a question on #sqlhelp hashtag for fetching database properties using Powershell. There are multiple posts out there on the web to do this using SMO. A crude way would be to use the Invoke-Sqlcmd cmdlet to do this.


Invoke-Sqlcmd -Query "SELECT filename,size,dbid FROM sys.sysaltfiles;"

If you wanted a cleaner output or some post processing done on the results fetched and wanted to use foreach, them this could also be done:


$dbprop = Invoke-Sqlcmd -Query "SELECT filename,size,dbid FROM sys.sysaltfiles;"
foreach ($db in $dbprop)
Write-Host $db.filename

If you are using SQLPS, then the above command to give the information that you want by invoking SQLCMD using Powershell.

Other ways to do this are mentioned here:

Get SQL database size using Windows Powershell

Get database properties using PowerShell in SQL Server 2008 by Tim Chapman (Blog)