Can I export and make the Stored procedure refence the new DB name?

[liamlaurent]

Good day everyone,

I am having trouble exporting an SQL server database to another database on the same server - but with a different name. Whenever I export the DB the stored procedures maintain their references to the original database name. Is there a way that I can export the DB and make the stored procedures reference the new DB's name.

(ex) I wanna export the DB called db_HelloWorld to db_GoodByeWorld. There is a stored procedure in db_HelloWorld that reads as follows:

CREATE PROCEDURE [delete_tb_photo_1]
(@id_1 [int])
AS DELETE [db_HelloWorld].[dbo].[tb_photo]
WHERE
( [id] = @id_1)
GO

Can I export it so that it will automatically read:

CREATE PROCEDURE [delete_tb_photo_1]
(@id_1 [int])
AS DELETE [db_GoodByeWorld].[dbo].[tb_photo]
WHERE
( [id] = @id_1)
GO

Any help would be appreciated.

Liam

 

[ptheriault]

The export can not do that. you will need to update them manually. I would script out all my procedures to one file and use the find and replace editor to do a mass update. Then run the script on the new database.

- Paul
- Database performance looks fine, it must be the Network!

 

[liamlaurent]

Thank you Paul,

I just removed the [db_HelloWorld].[dbo]. part before the table name and the exported like that. It seems like this part of the extended table name is not needed by SQL Server.

Thanks
Liam

 

[ptheriault]

no it is not needed. unless you have tables with the same name but different owners. then you nrrd the dbo.tablename or owner.tablename

- Paul
- Database performance looks fine, it must be the Network!