In Powershell....exporting to Excel
$Path = "C:\temp\Get-Indexes.sql"
$tsql = Get-Content -Path $Path -Raw
foreach( $ServerInstance in $ServerInstances)
{
$SmoServer = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerInstance ;
$SmoDatabase = New-Object Microsoft.SqlServer.Management.Smo.Database
$Indexes = @()
foreach($Database in $($SmoServer.Databases | Where-Object {!($_.IsSystemObject) -And $_.name -ne "tobeexcluded"}| Select -ExpandProperty name) )
{
$Batch = New-Object -TypeName:Collections.Specialized.StringCollection
$Batch.AddRange($tsql)
$SmoDatabase = $SmoServer.Databases.Item($Database)
$indexes += $SmoDatabase.ExecuteWithResults($Batch).Tables[0]
}
$WorkSheet = "Indexes_$($ServerInstance.Replace('\','_'))"
$IndexReport = "C:\temp\Indexes.xlsx"
$Indexes | Select -Property Database, TableName, IndexName,IndexColumns,IncludeColumns | Export-Excel $IndexReport -WorkSheetName $WorkSheet -FreezeTopRow -BoldTopRow
}
Iain