If you run across migrating or copying a database structure for some purpose, yet need to change the database references or some other string value inside all the procedures and functions to point to the newly named object, you are in for a lot of work!

I built this procedure to search all procedures and functions, and script the replacement across multiple databases, to streamline this type of conversion. I’ll post up one for views and synonyms later, as my time was limited to post this. In my case, this script was built to replace DB1 with DB2, and I had to accomplish this across several databases at once. This script might help save you some time!

/*******************************************************
PROCEDURE TO REPLACE ALL REFERENCES TO PARTICULAR DATABASE WITH NEW DATABASE NAME IN SERVER
REPLACE: REPLACESERVERNAME1 with your server name (mod to use for multiple servers)
REPLACE:  DBTOSEARCH1  with your database to search and replace in 
REPLACE:  DBTOSEARCH2  with your database to search and replace in 
REPLACE:  DBTOSEARCH3  with your database to search and replace in 
REPLACE:  DBTOSEARCH4  with your database to search and replace in 
2013-08-13sheldonh@ 07:38:04: created 
*******************************************************/set xact_abort on
if object_id( 'tempdb..#dblist' ) is not null
drop table #dblist;
create table #dbList
(
server_namesysname
,database_namesysname
,database_idint
)
if object_id( 'tempdb..#procCommands' ) is not null
drop table #procCommands;
create table #procCommands
(
unique_temp_kintprimary key identity (1, 1) not null
,server_namesysname
,database_namesysname
,full_object_namesysname
,original_command_to_executenvarchar(max)
,modified_command_to_executenvarchar(max)
,executed_commandnvarchar(max)
,is_functionbit
,errorvarchar(max)
)
/*******************************************************
SERVER - DATABASE LIST (can modify syntax, add new cursor to make it run on multiple servers)
*******************************************************/insert into #dbList
(
server_name
,database_name
,database_id
)
output 'Database List' as output_clause_description, inserted.*
select
'REPLACESERVERNAME1'
,name
,database_id
from
REPLACESERVERNAME1.master.sys.databases with (nolock)
where
name in ('DBTOSEARCH1', 'DBTOSEARCH2', 'DBTOSEARCH3', 'DBTOSEARCH4')
and State_desc = 'ONLINE'
and Is_In_Standby = 0
/*******************************************************
get all stored procedures & functions in table
*******************************************************/declare@ServerNamesysname
,@DbNamesysname
,@DbIDint
,@DbIDVarcharvarchar(10)
declare proc_cursor cursor fast_forward read_only local for
select
server_name
,database_name
,cast( database_id as varchar(10) ) as database_id
from
#dbList
open proc_cursor
fetch next from proc_cursor into @ServerName, @DbName, @DbID;
while @@fetch_status = 0
begin
if @ServerName = 'REPLACESERVERNAME1'
begin
declare @XSQL nvarchar(max) = convert( nvarchar(max),
'BEGIN
use ' + @DbName + ';
insert into #procCommands
(
server_name
,database_name
,full_object_name
,original_command_to_execute
,is_function
)
select
server_name = @@servername
,database_name = db_name()
,full_object_name = object_schema_name( object_id, db_id()) + ''.'' + object_name( object_id, db_id() )
,original_command_to_execute = object_definition( object_id )
,is_function = 0
from
sys.sql_modules with (nolock)
where
objectproperty( object_id, ''IsProcedure'' ) = 1
and (
definition like ''%DBTOSEARCH1%''
or definition like ''%DBTOSEARCH2%''
or definition like ''%DBTOSEARCH3%''
or definition like ''%DBTOSEARCH4%''
)
insert into #procCommands
(
server_name
,database_name
,full_object_name
,original_command_to_execute
,is_function
)
select
server_name = @@servername
,database_name = db_name()
,full_object_name = object_schema_name( object_id, db_id() ) + ''.'' + object_name( object_id, db_id() )
,original_command_to_execute = object_definition( object_id )
,is_function = 1
from
sys.objects
where
type in (''IF'', ''TF'')
and (
object_definition( object_id ) like ''%DBTOSEARCH1%''
or object_definition( object_id ) like ''%DBTOSEARCH2%''
or object_definition( object_id ) like ''%DBTOSEARCH3%''
or object_definition( object_id ) like ''%DBTOSEARCH4%''
)
END
'
)
exec (@XSQL);
end
fetch next from proc_cursor into @ServerName, @DbName, @DbID;
end
close proc_cursor
deallocate proc_cursor
/*******************************************************
string manipulations, seperated out for clarity, ease of editing instead of nesting in multiple steps
Simple replacement, this changes any references to the string, so make sure if you are replace a database called 
"AND" that you filter accordingly else all AND's would be replaced. 
*******************************************************/update #procCommands
set modified_command_to_execute = original_command_to_execute
update #procCommands
set modified_command_to_execute = replace( modified_command_to_execute, 'DBTOSEARCH1', 'REPLACEME' )
update #procCommands
set modified_command_to_execute = replace( modified_command_to_execute, 'DBTOSEARCH2', 'REPLACEME' )
update #procCommands
set modified_command_to_execute = replace( modified_command_to_execute, 'DBTOSEARCH3', 'REPLACEME' )
update #procCommands
set modified_command_to_execute = replace( modified_command_to_execute, 'DBTOSEARCH4', 'REPLACEME' )
/*******************************************************
since text of all stored procs will be very large, executing row by row in cursor
*******************************************************/go
declare@DbNamesysname
,@ModifiedCommandnvarchar(max)
,@FullObjectNamesysname
,@IsFunctionbit
,@TempKint
declare coolCursor cursor fast_forward read_only local for
select
c.unique_temp_k
,c.database_name
,c.modified_command_to_execute
,c.full_object_name
,c.is_function
from
#procCommands c
open coolCursor
fetch next from coolCursor into @TempK, @DbName, @ModifiedCommand, @FullObjectName, @IsFunction;
while @@fetch_status = 0
begin
declare @dropCommand nvarchar(max) = 'if object_id(''' + @FullObjectName + ''') is not null drop ' + case
when @IsFunction = 1 then 'function '
else 'procedure '
end + @FullObjectName + '; '
declare @output nvarchar(max) =
'
exec (''Use ' + @DbName + ' ; 
' + replace( @dropCommand, '''', '''''' ) + ' 
exec ( ''''' + replace( @ModifiedCommand, '''', '''''''''' ) + ''''')'')
'
-- save this compiled command into temp table for reference
update #procCommands
set executed_command = @output
where
unique_temp_k = @TempK
--select @output
print 'processing ' + @FullObjectName
begin try
--execute the dynamic sql statement
print 'entered try'
--exec (@output) -- EXECUTE DYNAMIC SQL CHANGES 
end try
begin catch
rollback transaction
declare @ErrorMsg varchar(max) = error_message()
declare @ErrorDate smalldatetime = getdate()
declare @ErrorSeverity int = error_severity()
declare @ErrorState int = error_state()
declare @ErrorLineNumber int = error_line()
declare @ErrorNumber int = error_number()
print 'ERROR CHANGING: ' + @FullObjectName
update #procCommands
seterror= 'Error Number: ' + convert( varchar(10), @ErrorNumber ) + '; Error Line Number: ' + convert( varchar(10), @ErrorLineNumber ) + '; Error Message: ' + @ErrorMsg
,executed_command= @output
where
unique_temp_k = @TempK
raiserror (
@ErrorMsg
, @ErrorSeverity
, @ErrorState
, @ErrorLineNumber
, @ErrorNumber
);
end catch
fetch next from coolCursor into @TempK, @DbName, @ModifiedCommand, @FullObjectName, @IsFunction;
end
close coolCursor
deallocate coolCursor
/*******************************************************
recommend backup of the temp table into table for temporary period to preserve changes
*******************************************************/select
'Successfully Processed'
,*
from
#procCommands c
where
c.error is null
select
'Error, Rollback Attempted'
,*
from
#procCommands c
where
c.error is not null
set xact_abort off