5

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

}
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.