$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.
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:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment