What's the best way to change hard coded database references in view definitions (SQL Server 2008 R2)?

I've created local copies of a related set of databases by restoring the backup of one set from another server under different names.

So I have taken bak files of databases named ABCMain and ABCDependent from another server and have restored them as XYZMain and XYZDependent on my local SQL Server instance.

Unfortunately, all the view definitions in the ___Dependendent DB have开发者_如何学编程 hard-coded database qualifiers pointing back to ABCMain like this:

CREATE VIEW dbo.[MyView] AS SELECT * FROM [ABCMain].dbo.MyTable

I need to drop "[ABCMain]" or change it to "[XYZMain]" in over 1000 views in XYZDependent. SQL Server doesn't allow altering system catalogs.

Any ideas?

Thanks,

John

You might want to consider using SQL Synonyms for the purpose of referencing tables in another database rather than your current view strategy. See this question for a discussion of the pros and cons. This won't solve your current problem, but you may find you get better performance with synonyms. What you really want is a SQL Synonym that references a database rather than a specific table, and you are not alone.

My preferred method to handle this problem would be to script all of your views using SQL Sever Management Studio:

1. Right-click the database and select Generate Scripts... from the Tasks menu
2. On the Choose Objects page, choose Select specific database objects and click the Views checkbox to select all views.
3. On the Set Scripting Options page, click the Advanced button and select Script DROP and CREATE as an option.
4. Modify the resulting script by searching and replacing the database name. Include the brackets and the dot (.) to avoid accidentally renaming other objects, e.g. replace '[ABCMain].' with '[XYZMain].'.

This isn't very elegant, but it gets the job done fairly quickly. Hopefully Microsoft will be adding database synonyms at some point.

You could do this in the .NET SMO framework pretty easily, I think. It would be like a 10 line powershell script. Pseudo-code:

<load the frameworks>

$ssrv = new-object "Microsoft.SqlServer.Management.SMO.Server" $Servname

$sdb = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$sdb = $ssrv.Databases[$DBName]

$sdb.Views | Foreach-object {$_.textbody = $_.textbody -replace '[MyOldDB]', '[MyNewDB]'}

You will need to look at the actual methods and properties, but it should be pretty straightforward.