SQL Server SMO and PowerShell formatting

Question

I have probably been looking at this for too long to figure this out...

1. What is the easiest way I could get this exported to a CSV format?
2. Would it be better to dump this to a table in a database more easily? I will be running this from a central server that I do have an instance running.

The function below is for the purpose of pulling information about one or multiple databases of a SQL Server instance. So far I know this works on SQL 2005 and up.

I have used Format-Table but that ends up cutting off columns. I have used Output-File which can work but defaults to a list view which could be a pain to get into Excel. It also cuts off column data that exceeds a certain length.

EDIT: Purpose of this function is to run it against 100+ servers for gathering inventory of each instance.

PowerShell Function:

# Load SMO
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
function Get-DatabaseInfo ($server,$dbname)
{
 $srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
 $db = $srv.Databases.Item($dbname)
 $DataFile = $db | Select -ExpandProperty FileGroups | Select -ExpandProperty Files
 $LogFile = $db | Select -ExpandProperty LogFiles
 $tables = $db | Select -ExpandProperty tables | ? {$_.IsSystemObject -eq $false}
 $indexes = $tables | Select -ExpandProperty Indexes | ? {$_.IsSystemObject -eq $false}
 $srv.Databases.Item($dbname) | Select @{Label="*****************Database Name*****************";Expression={$_.Name}}
 Write-Host "Database information for $dbname" -ForegroundColor red 
 $db |
 Select @{Label="DateCaptured";Expression={Get-Date -Format yyyyMMdd-HHmm}},
 ID, Name, Owner, CreateDate, 
 CompatibilityLevel, RecoveryModel, 
 LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate, LogReuseWaitStatus,
 ActiveConnections, 
 AutoClose, AutoShrink, 
 AutoCreateStatisticsEnabled, AutoUpdateStatisticsEnabled,
 Collation, 
 @{Label="DataSpaceUsage (KB)";Expression={$_.DataSpaceUsage}},
 @{Label="IndexSpaceUsage (KB)";Expression={$_.IndexSpaceUsage}},
 @{Label="SpaceAvailable (KB)";Expression={$_.SpaceAvailable}},
 @{Label="Size (MB)";Expression={$_.Size}},
 IsSQLCLREnabled,
 IsMirroringEnabled, PageVerify, ReplicationOptions
 Write-Host "Database File Level information for $dbname" -Foreground red
 $DataFile | Select Name, Filename, Growth, GrowthType,
 @{Label="MaxSize (MB)";Expression={$value = $_.MaxSize; switch($value){-1 {"Unlimited"} default {"{0:N2}" -f($value/1024)}}}},
 @{Label="SizeAllocated (MB)";Expression={"{0:N2}" -f($_.Size/1024)}},
 @{Label="UsedSpace (MB)";Expression={"{0:N2}" -f($_.UsedSpace/1024)}},
 @{Label="Data % Full";Expression={"{0:N2}" -f(($_.UsedSpace/$_.Size) * 100)}},
 @{Label="Data Space Left (MB)";Expression={"{0:N2}" -f(($_.Size/1024)-($_.UsedSpace/1024))}},
 @{Label="Data % Available";Expression={"{0:N2}" -f((($_.Size/1024)-($_.UsedSpace/1024)) / ($_.Size/1024))}}
 $logfile | Select Name, Filename, Growth, GrowthType,
 @{Label="MaxSize (MB)";Expression={$value = $_.MaxSize; switch($value){-1 {"Unlimited"} default {"{0:N2}" -f($value)}}}},
 @{Label="SizeAllocated (MB)";Expression={"{0:N2}" -f($_.Size/1024)}},
 @{Label="UsedSpace (MB)";Expression={"{0:N2}" -f($_.UsedSpace/1024)}},
 @{Label="Log % Full";Expression={"{0:N2}" -f(($_.UsedSpace/$_.Size) * 100)}},
 @{Label="Log Space Left (MB)";Expression={"{0:N2}" -f(($_.Size/1024)-($_.UsedSpace/1024))}},
 @{Label="Log % Available";Expression={"{0:N2}" -f((($_.Size/1024)-($_.UsedSpace/1024)) / ($_.Size/1024))}}
 Write-Host "Table Information for $dbname" -ForegroundColor red
 $tables | Select @{Label="DateCaptured";Expression={Get-Date -Format yyyyMMdd-HHmm}}, Name, RowCount, HasClusteredIndex | 
 Sort-Object RowCount -Descending
 Write-Host "Table Indexes Information for $dbname" -ForegroundColor Red
 $indexes | Select Parent, Name, IndexKeyType, IndexedColumns, FillFactor, SortInTempdb
}

• Aaron Bertrand: I don't suppose you've tried export-CSV?
• user507: I have. It only captures the first set for some reason. I would use inside the function but it does not have an append parameter.
• Aaron Bertrand: See dmitrysotnikov.wordpress.com/2010/01/19/export-csv-append
• Aaron Bertrand: Also this Connect item implies it is fixed, but in typical Microsoft fashion, doesn't explain when or where.
• user507: @AaronBertrand Apparently since each set is a different number of columns using export-csv with -append does not work. I noticed it was mentioned in the comments on Dmitry's blog post as well.

Answer 1

I did something similar at a previous job and we ended up defining tables on my central server and granting permissions to my (monitored) sql sever agent service accounts to those tables. Then we created a job and linked server on each monitored instance to the central server.

Answer 2

When it comes to PowerShell and SQL Server there is always different ways of doing or handling this scenario. I ended up just splitting them up as suggested by Aaron in the comments.

I don't want to delete this question so I am just answering it in this manner in case someone wants to use the scripts provided.

If a moderator feels that the question is better of removed, by all means go right ahead.