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: 

param
([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”
$Scripter.Options.DriPrimaryKey = $true

$Scripter.Options.ScriptDrops = $true

$scriptingOptions = 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.