Monday, April 16, 2012

Changing the database of all synonyms in a database

So, I came across a problem today at the client where a database had been restored from production into the QA environment, but there were synonyms in the database that pointed to an external database. So, the restored database's synonyms were pointing to the wrong place. I figured this would be easy enough. And it was... I just wish it had been easier. Specifically, I with the following had worked:

$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