Thursday, May 10, 2012

Changing the database of all synonyms - redux

In a previous post, I posted some code that should change the synonyms that point to one database and point them to another. I tried to use that code today and it didn't go so well. Specifically, powershell gave me some guff about changing the collection in the foreach loop. Here's the workaround that I came up with:
$server_name = '.';
$db = 'AdventureWorks';
$old_syn_db = 'old_db';
$new_syn_db = 'new_db';

$server = new-object Microsoft.SqlServer.Management.Smo.Server $server_name;
$db = $server.Databases[$db];

$old_synonyms = @();
$new_synonyms = @();

foreach ($syn in $db.Synonyms) {
    if ($syn.BaseDatabase -eq $old_syn_db) {
        $s = new-object Microsoft.SqlServer.Management.Smo.Synonym
        $s.Schema = $syn.Schema
        $s.Name = $syn.Name
        $s.Parent = $db
        $s.BaseObject = $syn.BaseObject
        $s.BaseSchema = $syn.BaseSchema
        $s.BaseDatabase = $new_syn_db
        $old_synonyms += $syn
        $new_synonyms += $s;
    }
}

foreach ($syn in $old_synonyms) {
   $syn.Drop();
}

foreach ($syn in $new_synonyms) {
   $syn.Create();
}

Hope that helps.

No comments:

Post a Comment