best way/product to handle db rename in sp objects

[timc]

My developers have an application using 3 databases called
mardemo,mardemowh,mararchive. However when the solution is deployed to the user the db's name will be changed per client. Since several sp.'s have joins and other processes that use the dbname, they have to be changed for each client. Currently it is a manual update. Is there a better solution anyone knows up that will not affect performance?

[skhanal]

Since you have names hard coded in the stored procedures there is no simple way to do it.

You could build your installer to change the stored procedures code at installation time.

[MAK]

Assuming you have script to run at the time of installation, you could write a simple vbscript that could replace all the database name in your script with the new databasename. it doenst take that long to replace strings.

[mikr0s]

/*
Note: Use the following procedure at your OWN RISK.
Uses technique that is not recommended.
Procedure should be placed in master database.
Several controls can be implemented, since execution of a dynamic string is used (sp_executesql could be used instead)

!!! Procedure/Code is not tested thoroughly!!!!
*/

USE master
GO

/*
Renames an existing database and updates code references to db name.
*/

CREATE PROCEDURE a_my_renamedb
@old_name nvarchar(50) = NULL,
@new_name nvarchar(50) = NULL
AS
DECLARE @error INT
declare @sql nvarchar(4000)

--check if user supplied a name of a database
IF @old_name IS NULL
BEGIN
SELECT 'No database selected' AS msg
RETURN
END

IF @old_name = 'master'
BEGIN
SELECT 'Wrong choice.' AS msg
RETURN
END

--check if @old_name db exists
IF NOT EXISTS(SELECT 1 FROM master..sysdatabases WHERE name = @old_name)
BEGIN
SELECT 'Database ['+ @old_name +'] does not exist.'
RETURN
END

--check if a new db name was specified
IF @new_name IS NULL
BEGIN
SELECT 'No new name for database selected' AS msg
RETURN
END

--set database to single user
SET @sql = 'ALTER DATABASE ['+ @old_name +'] SET SINGLE_USER'
EXECUTE (@sql)

--rename existent database
EXEC @error = sp_renamedb @dbname = @old_name, @newname = @new_name
IF @error <> 0
BEGIN
SET @sql = 'ALTER DATABASE ['+ @old_name +'] SET MULTI_USER'
EXECUTE (@sql)

SELECT 'Failed to rename db.' AS msg
RETURN
END

--configure server to allow updates to "system tables"
EXEC sp_configure 'allow updates' , 1
RECONFIGURE WITH OVERRIDE

--update syscomments table
--first look for @old_name+'.'
SET @sql = '
UPDATE ['+@new_name+']..syscomments
SET ctext = CONVERT(VARBINARY(8000), REPLACE(ctext, '''+@old_name+'.'', '''+@new_name+'.''))
WHERE text LIKE ''%'+@old_name+'.%'''

EXECUTE (@sql)


--update syscomments table
--.. look for @old_name+'].'
SET @sql = '
UPDATE ['+@new_name+']..syscomments
SET ctext = CONVERT(VARBINARY(8000), REPLACE(ctext, '''+@old_name+'].'', '''+@new_name+'].''))
WHERE text LIKE ''%'+@old_name+'].%'''

EXECUTE (@sql)


--configure server NOT to allow "system tables" updates
EXEC sp_configure 'allow updates' , 0
RECONFIGURE WITH OVERRIDE

--set renamed database to multi user
SET @sql = 'ALTER DATABASE ['+ @new_name +'] SET MULTI_USER '
EXECUTE (@sql)


--HTH--