Yep.
function Get-IndexKeyWidth{
param(
[string]$ServerName,
[int]$Port = 2080,
[string]$DatabaseName,
[string]$TableName
)
begin{
}
process{
$objects = New-Object System.Collections.Generic.List[object]
$indexes = Invoke-Sqlcmd -ServerInstance "$ServerName,$Port" -Database $DatabaseName -Query "EXEC sp_helpindex [$TableName]"
$indexes | %{
$sb = New-Object System.Text.StringBuilder
$indexName = $_.index_name
$_.index_keys -split ',' | %{
[void]$sb.Append("'$($_.ToString().Trim())',");
}
$cmd = "SELECT SUM(max_length)AS TotalIndexKeySize
FROM sys.columns
WHERE name IN ($($sb.ToString().Substring(0, $sb.ToString().Length-1)))
AND object_id = OBJECT_ID(N'$TableName');"
$cmd
Invoke-Sqlcmd -ServerInstance "$ServerName,$Port" -Database $DatabaseName -Query $cmd | %{
[void]$objects.Add([PSCustomObject]@{
ServerName = $ServerName
DatabaseName = $DatabaseName
TableName = $TableName
IndexName = $indexName
SizeBytes = $_.TotalIndexKeySize
});
}
}
$objects
}
end{
}
}
Leave a comment