Changing stored procedures on the fly

[Padre764]

Hello,

Here is my problem: I am working on a product where the databases that we send to our customers always the same names, FooDB, TrackDB for example. When we troubleshoot these databases, we restore them to one of servers here in the office and rename the databases to the customer's name so that we can track which database is which. The problem is that each database has a hardcoded reference to the other database in the stored procedures. Is there a way to update these references? I know that the text of stored procedures in stored in syscomments but I have never heard of making changes to this table - some like:
UPDATE syscomments
SET text = replace(text, FooDB, CustomerDB)
We have been going to Enterprise Manager, generate the scripts from there, editting them, and them reloading them. If there is an easier way to do that I would like to know.

Thanks,
padre

 

[bborissov]

You can always recreate you procedure on the fly:

USE YourDB
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'yourSPname' AND type = 'P')
   DROP PROCEDURE yourSPname
GO

CREATE PROCEDURE yourSPname
.....
GO

Borislav Borissov