Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

}
share|improve this question
    
I don't suppose you've tried export-CSV? –  Aaron Bertrand Dec 19 '12 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. –  Shawn Melton Dec 19 '12 at 17:31
1  
    
Also this Connect item implies it is fixed, but in typical Microsoft fashion, doesn't explain when or where. –  Aaron Bertrand Dec 19 '12 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. –  Shawn Melton Dec 19 '12 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.

share|improve this answer
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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