Solved

Edit all views and stored Procedures

Posted on 2011-09-07
4
323 Views
Last Modified: 2012-05-12
We are planning to migrate to SQL Server 2008 from 2005. We also wanted to change the database names while doing this.But the problem is, we have these database names hard-coded in the views and stored procedures.
Can we write a script to rename all the database names inside these views and stored procedure instead of going to each one of them and change it ?
I tried to Generate scripts using SSMS for all the ones that I need to change and replaced the database names using the text editor and then ran this script in Management Studio which did not work.
Any ideas ?

Thanks,
TH

0
Comment
Question by:supportrp
4 Comments
 
LVL 15

Assisted Solution

by:faiga16
faiga16 earned 167 total points
ID: 36497022
0
 
LVL 6

Accepted Solution

by:
markterry earned 167 total points
ID: 36497026
That should work.

First off ... Do a backup.

Generate Scripts, then change the database name on the actual database, then do a find and replace on the generated script (must be an overwriting script, like drop and create, or alter) and replace prior database name with new database name.

This will not work if you do not change the database name before running the script.
0
 
LVL 5

Assisted Solution

by:DavidMorrison
DavidMorrison earned 166 total points
ID: 36497031
Hi

In ssms right click the DB, select tasks then generate scripts. this will let you script the entire DB into a new query window. Once it has done this just do a find and replace of the old DB name to the new one and you're done

Thanks

Dave
0
 
LVL 1

Author Closing Comment

by:supportrp
ID: 36498923
Synonym option doesn't work for my scenario though it is one of the ways. I will have to try the other option after the migration. But I am sure this will work.

Thanks for the quick responses!


TH
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question