Question

我用不同名称从另一个服务器恢复一个数据库的备份,从而创建了一套相关数据库的当地版本。

因此,我从另一个服务器接收了名为ABCMain和ABCDependent的数据库,并恢复了这些数据库,作为SYZMain和XYZDependent在我的当地服务器上。

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.

任何想法?

感谢

John

Answer 1

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:

Right-click the database and select Generate Scripts... from the Tasks menu
On the Choose Objects page, choose Select specific database objects and click the Views checkbox to select all views.
On the Set Scripting Options page, click the Advanced button and select Script DROP and CREATE as an option.
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]. .

这很不合法,但这项工作很快完成。希望微软在某个时候添加数据库的同义词。

Answer 2

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] }

你们需要看看实际的方法和财产,但应该直截了当。

友情链接