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

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,
            AutoClose, AutoShrink, 
            AutoCreateStatisticsEnabled, AutoUpdateStatisticsEnabled,
            @{Label="DataSpaceUsage (KB)";Expression={$_.DataSpaceUsage}},
            @{Label="IndexSpaceUsage (KB)";Expression={$_.IndexSpaceUsage}},
            @{Label="SpaceAvailable (KB)";Expression={$_.SpaceAvailable}},
            @{Label="Size (MB)";Expression={$_.Size}},
            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

  • I don't suppose you've tried export-CSV? Dec 19, 2012 at 17:21
  • 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.
    – user507
    Dec 19, 2012 at 17:31
  • 1
  • Also this Connect item implies it is fixed, but in typical Microsoft fashion, doesn't explain when or where. Dec 19, 2012 at 17:32
  • @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.
    – user507
    Dec 19, 2012 at 18:15

2 Answers 2


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.


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 and acknowledge you have read our privacy policy.