Hi, I need to change the database name in SQL SERVER 2008 and use it in another project. However it consist hundreds of stored procedures and the name of the database should be changed in the stored procedures as well. Is there any way to do this?
A:
There is no way of doing this automatically. You would have to manually change every stored procedure.
Do you really need to change the name?
Barry
2010-06-08 06:59:06
+10
A:
If you right mouse click a Database and choose Tasks->Generate Scripts. Go through the Wizard and it will create a SQL script for you. Make sure that you select all of the necessary options e.g. Create Database, Stored Procedures etc. Once finished, you'll have a big script. Find and replace the database name.
Ardman
2010-06-08 07:01:12
+1. When editing the script I would suggest that you remove the database name unless it is needed. Having the SPs of a DB knowing the DB name can give nasty effects if someone makes a copy of the db with another name for test purposes (the SPs of the test DB could update the live DB).
Anders Abel
2010-06-08 07:04:26
Thank you for the quick answer. I'll give it a shot asap.
Semih
2010-06-08 07:04:52
Just a clarification, because I'm a noob and I stared at this answer for a good ten minutes before realising what was going on. The "Generate Scripts" thing creates a big text file within the SQL editor. Find/Replace the database name on all the entries in that file, then click Execute. It will fail because the script tries to re-insert all of your existing stored procedures into the database. You will now have to delete all of the procedures you're updating, execute again, and they will be re-inserted, and it will look like nothing has happened. Now refresh the view and they will reappear.
Frosty840
2010-10-22 11:33:46
Instructions given in a weird order because any set of instructions where the second line is "Now delete all of the stored procedures from your database" is stupid. My instructions at least result in an error message which tells you why you need to delete them :P
Frosty840
2010-10-22 11:35:41
+2
A:
I would suggest that you export all your stored procedures as sql-files and then take a nice texteditor (like Notepad++) and make a file-search&replace-action to change all the names referenced inside the sql-files.
There is no other way around as far as I can say :-(
Olaf Watteroth
2010-06-08 07:01:42
+1
A:
I'd make next thing: I'd generate all scripts for database by Management Studio(Right Click on DB -> Tasks -> Generate scripts), after that I'd replace name of database (Ctrl + H).
Pavel Belousov
2010-06-08 07:03:21
A:
Wouldn't it be better to keep everything the same and use a different server or server instance? Are you going to have to maintain database changes through these two datbases with the different names?
HLGEM
2010-06-08 17:43:18