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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.