PowerShell Script for Creating Indexes

January 21, 2010 at 12:14 pm (PowerShell, SQL Server 2005, SQL Server 2008, TSQL) (, , , )


I needed to create an identical index on a bunch of tables within one of my projects (yes, I know this is problematic on multiple levels and I’m working on that too). Rather than sitting around typing this up, I decided to use PowerShell to do the work for me. I’m still very much learning how to do things in PowerShell so this took me almost as long as it would have to type them up, but now I know more than I did.

Having gone through the pain of trying to find a good example on the web that did exactly what I wanted (they’re out there, just hard to find), I decided I’d add this one in so the next person had at least one more source of information.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.Smo") | out-null
Set-Location C:\Scripts
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'
$database = $server.Databases["MyDB"]
foreach($table in Get-Content "tables.txt")
{
    #Set the table to the latest one from the list need to make this more generic
    $currenttable = $database.Tables.Item($table.TrimStart("SCH."),$table.substring(0,3))
    
    #create an index, linked to the table
    $index = new-object Microsoft.SqlServer.Management.Smo.Index #($currenttable)
    $index.name = ("ix_"+$table.Replace(".","_"))
    $index.Parent = $currenttable
    
    #create the columns & set their properties
    $col = new-object Microsoft.SqlServer.Management.Smo.IndexedColumn
    $col2 = new-object Microsoft.SqlServer.Management.Smo.IndexedColumn
    $col.Name = "MyFirstColumn"
    $col2.Name = "MySecondColumn"
    $col.Parent = $index
    $col2.Parent = $index
    
    #add the columns to the index
    $index.IndexedColumns.Add($col)
    $index.IndexedColumns.Add($col2)
    
    #add the index to the table
    $currenttable.Indexes.Add($index)
}

Basically the script opens a file and walks through the entries in the file. Make sure you don’t have extra carriage returns or blank lines or it’ll try to find blank tables which will raise an error (error handling is my next task). You have to set the $currenttable using either the Item or use the Where-Object function. The documentation on that is either flat out wrong, or there’s a bug. Once you’ve got the table set, it’s pretty simple. Create an Index object, IndexedColumn objects and add the IndexedColumns to the Index and the Index to the table. You’re done.

I created about 50 indexes in almost no time with the script and it would have taken quite a bit longer to do manually. That’s not counting the time pounding my head against the wall because the documented syntax wouldn’t work.

Easy stuff, totally straight-forward, but if you’re trying to learn PowerShell, things like this seem daunting.

Thanks to @BenchmarkIT, @Laertejuniordba (who has a new article on Simple Talk and will be writing one for SQL Server Standard) and @cmille19, who all contributed to getting me over the hurdle on create the table object. Read everything that Allen White writes and commit it to memory.

1 Comment

  1. Tweets that mention PowerShell Script for Creating Indexes « Home of the Scary DBA -- Topsy.com said,

    […] This post was mentioned on Twitter by Grant Fritchey, Laerte Junior. Laerte Junior said: Excellent RT @GFritchey: PowerShell Script for Creating Indexes: I needed to create an identical index on a bunch ….. http://bit.ly/8dNBvi […]

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: