Get Index Key Length via Powershell

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{

    }
}
Advertisements

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: