Day: December 12, 2011

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
		$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