1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John Spiegel
  • 1,861
  • 3
  • 24
  • 39

2 Answers2

3

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.

Community
  • 1
  • 1
Paul Keister
  • 12,851
  • 5
  • 46
  • 75
  • I'm leary of DROP and CREATE because I need to retain the data (I'm KIDDING!). Seriously, thanks Paul. Took all of 10 minutes and I had 977 such references cleared out. – John Spiegel Jul 20 '11 at 21:22
1

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.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • Thanks, JNK. Looks very promising. As I've not looked into .NET SMO framework and will personally run into this once every 1/2 to 3 months, I'm going the more brute-force route. – John Spiegel Jul 20 '11 at 21:21