Export Index Sizes to Excel in Powershell

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
# Close Excel and clean up
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($item) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null
Remove-Variable objExcel
Remove-Variable wb