$server = new-object Microsoft.SqlServer.Management.Smo.Server '.';
$db = $server.Databases['AdventureWorks2008R2'];
foreach ($syn in $db.Synonyms) {
if ($syn.BaseDatabase -eq 'OldOtherDB') {
$syn.BaseDatabase = 'OtherDb';
$syn.Alter();
}
}
But it doesn't. In theory, SMO could make it happen by dropping and re-creating the synonym, but it doesn't. I don't know why. Oh well. I bend the computer to my will, not vice versa. So here's what I came up with:
$server = new-object Microsoft.SqlServer.Management.Smo.Server '.';
$db = $server.Databases['AdventureWorks2008R2'];
foreach ($syn in $db.Synonyms) {
if ($syn.BaseDatabase -eq 'OldOtherDB') {
$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 = "OtherDB"
$syn.Drop();
$s.Create();
}
}
Even with all of this, I love synonyms. They're implemented at my current client on my suggestion. Before, they'd hardcoded the external references into stored procedures. Which makes cross-environment restores "fun". After I ran the above script, everything was back to normal. In a lot less time than if I'd had to have updated all of the stored procedures by hand.
Update: A newer version can be found here.
No comments:
Post a Comment