Powershell for Batch Operations

March 1, 2010 at 7:30 am (PowerShell) (, , )

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”
$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.


  1. @SQLAJ said,

    Curious. Are you using Powershell to connect to oracle and convert the objects? Or just using it to clean the SQL database after conversion?
    We are using Sql Server Migration Assistant – SSMA (not my choice)
    to convert Oracle SQL 2008. The generated scripts are full of “crap” and I am using PS to parse the file and remove the extra code.

    I would rather use a different way to convert and am exploring possibilities.


  2. scarydba said,

    No. The actual reverse engineering was done with ERStudio. We connected to Oracle, peeled out the structures and then just converted them straight to SQL Server.

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 )

Connecting to %s

%d bloggers like this: