SQL Server SMO and PowerShell formatting

Question

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

What is the easiest way I could get this exported to a CSV format?
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

}

score 0 · Answer 1 · answered Jan 07 '13 at 18:32

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.

score 0 · Accepted Answer · answered Jan 08 '13 at 16:55

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.

SQL Server SMO and PowerShell formatting

2 Answers2