How To Update Hard Coded Database References In Stored Procedures ?

Hi There,

Our company deals with financial education and typically has 9 different databases which have some cross referenced stored procedures. Every time we replicate Production database into TEST and DEV environments, we had to manually update the database references in Stored procedures. and it usually takes atleast a week and until then all the dev and test work has to wait.

Hence, I wanted to write a script, Here the code below.


-- These two variables must contain a valid database name.
DECLARE @vchSearch VarChar(15),
@vchReplacement VarChar(15)

SET @vchSearch = 'Search'
SET @vchReplacement = 'Replacement'
/*
-- Select the Kaplan Database Names in the Current Server
*/

DECLARE @tblDBNames TABLE (vchDBName VarChar(30))
INSERT INTO
@tblDBNames
SELECT
Name
FROM
MASTER.DBO.SYSDATABASES
WHERE
Has_DBAccess(Name)=1
And Name IN ( 'DB_DEV', 'DB_TEST', 'DB_PROD', 'WEBDB_DEV', 'WEBDB_TEST', 'WEBDB_PROD' , 'FINDB_DEV', 'FINDB_TEST', 'FINDB_PROD')

--SELECT * FROM @DBNames

IF @vchSearch NOT IN (SELECT vchDBName FROM @tblDBNames)
BEGIN
PRINT 'Not a Valid Search DB Name'
GOTO Terminate
END
IF @vchReplacement NOT IN (SELECT vchDBNAME FROM @tblDBNames)
BEGIN
PRINT 'Not a Valid Replacement DB Name'
GOTO Terminate
END

-- We have Valid DB Names, lets proceed...
--USE @vchReplacement

SET @vchSearch = '%' + @vchSearch + '..%'
SET @vchReplacement = '%' + @vchReplacement + '..%'

-- Get Names of Stored Procedures to be altered
DECLARE @tblSProcNames TABLE (vchSPName VarChar(100))

INSERT INTO
@tblSProcNames
SELECT
DISTINCT so.Name
FROM
SYSOBJECTS so
INNER JOIN SYSCOMMENTS sc
ON sc.Id = so.Id
WHERE
so.XType='P'
AND sc.Text LIKE @vchSearch
ORDER BY
so.name

-- Now, the table @tblSprocNames has the names of stored procedures to be updated.
-- And we have to Some HOW ?!! grab the stored proc definition and use REPLACE() to
-- update the database reference
-- Then, use cursors to loop through each stored proc and upate the reference



Now, I have got stuck how to extract the body of a stored procedure into a variable.


Please Help.... I dont want spend weeks of time in the future to do this work manually.

Madhu