Now right off the bat I'm going to say that I'm in no way saying this is the best way to do this. I'm 99% sure someone is probably going to lambaste me over this code. I wrote this in a hurry to fill an immediate need. However, with a little altering this script could be pretty useful, and is a good exercise in how to retrieve the actual code for a stored procedure or view using T-SQL. Before you even think about running this code though, you should know the following:

• This procedure is dangerous
• You'd be a fool to run it before backing up the db you're planning to run it on.
• If this proc is run with parameters that make any proc or view in the db invalid. That invalid object will be dropped from the db, and you will not be notified, nor will it be logged. (ie, replacing a table name with a table that does not exist, or changing a 3part name to a 3 part name that doesn't exist because the db hasn't been created yet. etc)
• This was written on SQL Server 2008, I have no idea if it works on previous versions or versions to come...I suspect not.

So, if I haven't scared you off yet, here's how this works.

1. Set the @p_CurrentName parameter equal to the text you want replaced.
2. Set the @p_NewName parameter equal to the text you would like it replaced with.
   See the execution example in the procedure comments below for further detail.
   I can say I've used this proc on several occasions to change 3 part names when moving code between environments, with no problems at all.

Now right off the bat I’m going to say that I’m in no way saying this is the best way to do this. I’m 99% sure someone is probably going to lambaste me over this code. I wrote this in a hurry to fill an immediate need. However, with a little altering this script could be pretty useful, and is a good exercise in how to retrieve the actual code for a stored procedure or view using T-SQL. Before you even think about running this code though, you should know the following:

So, if I haven’t scared you off yet, here’s how this works.

1. Set the @p_CurrentName parameter equal to the text you want replaced.
2. Set the @p_NewName parameter equal to the text you would like it replaced with.
   See the execution example in the procedure comments below for further detail.
   I can say I’ve used this proc on several occasions to change 3 part names when moving code between environments, with no problems at all.

CREATE PROCEDURE [dbo].[usp_ChangeDBThreePartNames]

@p_CurrentName VARCHAR (50), @p_NewName VARCHAR (50)

AS

BEGIN –procedure

/*–============================================================

Created By: Tavis Lovell

Created On: 2010.02.05

Description: This procedure can be used to change all of the 3

part name references (ex: dbname.schema.table) in an entire database

The procedures does this by searching for any procedures or views

that contain the contents of @p_CurrentName and then loops through

each returned proc or views sp_helptext and builds a string that

replaces the value of @p_CurrentName with @p_NewName. Once all lines

of the object have been concatenated, the procedure drops the old object

and then creates the new object with the replaced values.

NOTE! when replacing the 3part name, the new 3part name must be valid,

or the object (proc or view) will be dropped and not recreated.

Execution example:

EXEC usp_ChangeDBThreePartNames

@p_CurrentName = ‘BlogExamples.dbo’,

@p_NewName = ‘BlogExamples2.dbo’

ModifiedBy Date Description

—————————————————————-

Tavis Lovell 2010.02.05 Created

–============================================================*/

–declare local variables

DECLARE

@v_MinLoopProc INT,

@v_MaxLoopProc INT,

@v_MinLoopText INT,

@v_MaxLoopText INT,

@objname VARCHAR(255),

@v_Procedure VARCHAR(MAX),

@v_DropProcedure VARCHAR(500)

–===================================================

—————-CREATE STAGE TABLES——————

–===================================================

–this table is used to store the proc text

–while we change it

CREATE TABLE

#sp_helpText

(

idgk int identity(1,1),

tmptext nvarchar(4000)

)

–===================================================

———-STAGE ALL PROCEDURES TO CHANGE————-

–===================================================

DECLARE @ProcedureNames TABLE

(

idgk INT IDENTITY(1, 1),

RoutineType VARCHAR(50),

Name VARCHAR(255)

)

–also insert all the view names

INSERT INTO

@ProcedureNames

SELECT DISTINCT

‘View’,

v.name

FROM

sys.views v

–insert all the procedure names

INSERT INTO

@ProcedureNames

SELECT

‘Procedure’,

ROUTINE_NAME

FROM

INFORMATION_SCHEMA.ROUTINES

WHERE

ROUTINE_TYPE=‘PROCEDURE’

AND

ROUTINE_NAME != ‘usp_ChangeDBThreePartNames’

–===================================================

—————LOOP THROUGH EACH PROC—————-

–===================================================

–set pre loop vars

SET @v_MinLoopProc = (SELECT MIN(idgk) FROM @ProcedureNames)

SET @v_MaxLoopProc = (SELECT MAX(idgk) FROM @ProcedureNames)

WHILE (@v_MinLoopProc <= @v_MaxLoopProc)

BEGIN –loop through proc names

–clear #sp_helptext

TRUNCATE TABLE #sp_helptext

–set object name

SET @objname = (SELECT pn.Name FROM @ProcedureNames pn WHERE pn.idgk = @v_MinLoopProc)

–stage up proc text

INSERT INTO

#sp_helptext

EXEC

sp_helptext @objname

–set preloop vars

SET @v_MinLoopText = (SELECT MIN(idgk) FROM #sp_helpText)

SET @v_MaxLoopText = (SELECT MAX(idgk) FROM #sp_helpText)

–clear out procedure var

SET @v_Procedure = ”

–loop through all procedure lines

WHILE (@v_MinLoopText <= @v_MaxLoopText)

BEGIN –loop through text

–replace instances of the db reference

UPDATE

#sp_helpText

SET

tmptext = (SELECT REPLACE(t.tmptext, @p_CurrentName, @p_NewName) FROM #sp_helpText t WHERE idgk = @v_MinLoopText)

WHERE

idgk = @v_MinLoopText

–concat to proc var

SET @v_Procedure = @v_Procedure + (SELECT t.tmptext FROM #sp_helpText t WHERE idgk = @v_MinLoopText)

–increment text loop

SET @v_MinLoopText = (SELECT @v_MinLoopText + 1)

END –loop through text

–show

SET @v_DropProcedure = ”

IF ((SELECT pn.RoutineType FROM @ProcedureNames pn WHERE idgk = @v_MinLoopProc) = ‘Procedure’)

BEGIN – Drop the procedure

SET @v_DropProcedure = ‘DROP PROCEDURE ‘ + (SELECT pn.Name FROM @ProcedureNames pn WHERE idgk = @v_MinLoopProc)

EXEC (@v_DropProcedure)

END – Drop the procedure

ELSE IF ((SELECT pn.RoutineType FROM @ProcedureNames pn WHERE idgk = @v_MinLoopProc) = ‘View’)

BEGIN –Drop view

SET @v_DropProcedure = ‘DROP VIEW ‘ + (SELECT pn.Name FROM @ProcedureNames pn WHERE idgk = @v_MinLoopProc)

EXEC (@v_DropProcedure)

END –Drop view

EXEC (@v_Procedure)

–increment proc loop

SET @v_MinLoopProc = (SELECT @v_MinLoopProc + 1)

END –loop through proc names

–drop the temp table

DROP TABLE #sp_helpText

END

GO