Create an index with included columns in powershell

I had a need the other day to add a new index to a table that existed in multiple databases on multiple servers.  The index consisted of one indexed column and two included columns.  This script checks for existence of the index, and if it does not exist, adds the index.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$outputs = @();

$servers = 'ServerName'

try{
	$servers | %{
		$srvName = $_
		$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
		$srvConn.ServerInstance = $srvName
		$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

		$srv.Databases | where{-not $_.IsSystemObject} | %{
			$dbName = $_.Name
			$_.Tables | where{$_.Name -eq 'TableName'} | %{
				$indexes = $_.Indexes | where{$_.Name -eq 'IndexName'}
				if($indexes.Count -gt 0){
					$output = New-Object -TypeName PSObject -Property @{
					    ServerName = $srvName
					    DatabaseName = $dbName
					    TableName = $_.Name
						Message = 'Index already exists'
					}
					$outputs += $output
					continue;
				}
				
				#create new index object on current $_ table
				$index = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index -argumentlist $_, "IndexName"

				#add indexed column
				$idxCol = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -argumentlist $index, "IndexedColumn1", $true
				$index.IndexedColumns.Add($idxCol)
				
				#add included columns
				$incCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -argumentlist $index, "IncludedColumn1", $true
				$incCol1.IsIncluded = $true;
				$index.IndexedColumns.Add($incCol1)
				$incCol2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -argumentlist $index, "IncludedColumn2", $true
				$incCol2.IsIncluded = $true;
				$index.IndexedColumns.Add($incCol2)

				$index.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::None 
				$index.IsClustered = $false
				$index.FillFactor = 90
				$index.Create()
				
				$output = New-Object -TypeName PSObject -Property @{
				    ServerName = $srvName
				    DatabaseName = $dbName
				    TableName = $_.Name
					Message = 'Index added'
				}
				$outputs += $output
			}
		}
	}	
}
catch{
	$_ | fl -Force
}
finally{
	$outputs | SELECT ServerName, DatabaseName, TableName, Message | Out-GridView
}
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: