Update SQL Server 2005 view with new database name?

Question

I have approximately 100 SQL views that are a variation of this:

select * from RTC.dbo.MyTable

...now I find I need to change the name of the RTC table to something else. Rather than edit one view at a time, is there a way to script out all their drop/create statements to a text file so that I can do a global replacement?

Answer 1

In SSMS right click the database, go to Tasks and select there 'Generate Scripts...'. Select 'Views', select the views you want exported, export.

Answer 2

I'd use PowerShell. If you're not using SQL 2008 Client Tools, install them. Then get the PowerShell client, add the registered snapins (plenty of information out there on how to do that), and then use the directory structure to get to the folder representing your Views.

Then script them using something like:

Get-ChildItems | % {$_.Script()}

Use ScriptOptions to tell it to use an Alter script.

And replace "RTC." with the new database name... and run them using sqlcmd.

PowerShell actually becomes a really nice deployment option too.