Here’s a quick script to write your index sizes in KB to Excel in powershell. The code to write to Excel was pilfered from here. Obviously, this isn’t 100% accurate, as you’d have to execute the sys.dm_db_index_physical_stats function in detailed mode to get detailed information, but this is a good rough estimate.
$filePath = "c:\IndexSizes.xlsx"
if((Test-Path $filePath))
{
Remove-Item $filePath -Force
}
$objExcel = New-Object -ComObject Excel.Application
$wb = $objExcel.Workbooks.Add()
$item = $wb.Worksheets.Item(1)
$objExcel.Visible = $True
$i = 1
foreach($table in $db.Tables)
{
$item.Cells.Item($i,1) = $table.Name
foreach($idx in $table.Indexes)
{
$item.Cells.Item($i,2) = $idx.Name
$item.Cells.Item($i,3) = $idx.SpaceUsed
$i++
}
}
$wb.SaveAs($filePath)
# Close Excel and clean up
$objExcel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($item) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
Remove-Variable objExcel
Remove-Variable wb
Sql Server Powershell 
Leave a comment