SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Powershell for Batch Operations

I’m right in the middle of moving one of our databases from Oracle to SQL Server (and I just love saying that, over & over). Evidently the most common practice in Oracle is to leave all the tables as heaps. That’s a tad bit problematic approach for SQL Server. We moved the structure from Oracle to SQL Server with almost no changes and now we’re cleaning up the mess fixing the structure iterating the design. I needed to drop & recreate 250 plus indexes. 

Remember, I’m lazy. A few minutes bouncing around the web and looking at the wonderful book “SQL Server 2008 Administration with Windows Powershell” and I put together a simple little script that generates a script for dropping and recreating all the primary keys on the tables and, as a bonus, changes them to clustered (yes, I know, all primary don’t have to be clustered and there are possibly other good candidate keys… to misquote someone, compromises were made). Here’s the script: 


([string] $Server, [string] $Database, [string] $filepath)

# Connect to the server  

 [reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null 

 $Scripter= New-Object("Microsoft.SqlServer.Management.Smo.Scripter") 


$srv =New-Object "Microsoft.SqlServer.Management.Smo.Server" "$Server"

$db = $srv.Databases["$Database"] 

$Scripter.Server = $srv 


# define the output


$filepath =$filepath+"test.sql"


$scrcontent  = "use [$Database]" + "`r`n"+"GO"+"`r`n" 


.Options.DriPrimaryKey = $true


$Scripter.Options.ScriptDrops = $true 



= New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions  

$scriptingOptions.ScriptDrops = $true 


foreach ($Table in $db.Tables)
foreach($Index in $Table.Indexes)

if ($Index.IndexKeyType -eq "DriPrimaryKey" ) 


$scrcontent=$scrcontent + $Index.Script($scriptingOptions) +"`r`n"+"Go"+"`r`n"

$scrcontent=$scrcontent + $Index.script() +"`r`n"+"Go"+"`r`n"} } }



The only issue I ran into was the check to see if the index was clustered didn’t work when I tried passing the property type, so I had to use a string. No big deal, but it’s pretty cool how  what might have been a hard task was reduced to nothing. I’m digging PowerShell.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Posted by Anonymous on 2 March 2010

Pingback from  Dew Drop – March 2, 2010 | Alvin Ashcraft's Morning Dew

Leave a Comment

Please register or log in to leave a comment.