Renaming all references inside stored procedures and functions can be migraine worthy without a little help...
sql-server
Updated: 2016-03-18
Cleaned up formatting. This is older code limited to procs and functions. I’m sure there is a better way to do this now, but leaving here as it might help someone else in the meantime.
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!
Replace All references to database name in server to a new database name - Proc and functions only
/*******************************************************
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-13 sheldonh @ 07:38:04: created
2016-03-18 12:41 sheldonh changed to gist snippet. Lots of cleanup. Probably worth cleanup in future. This is older and did the job but probably could be done a lot simpler
*******************************************************/
set xact_abort on
if object_id('tempdb..#dblist') is not null
drop table #dbList;
create table #dbList
(
server_name sysname
,database_name sysname
,database_id int
)
if object_id('tempdb..#procCommands') is not null
drop table #procCommands;
create table #procCommands
(
unique_temp_k int primary key identity (1, 1) not null
,server_name sysname
,database_name sysname
,full_object_name sysname
,original_command_to_execute nvarchar(max)
,modified_command_to_execute nvarchar(max)
,executed_command nvarchar(max)
,is_function bit
,error varchar(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 @ServerName sysname
,@DbName sysname
,@DbID int
,@DbIDVarchar varchar(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%''
)
(
')
print @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 @DbName sysname
,@ModifiedCommand nvarchar(max)
,@FullObjectName sysname
,@IsFunction bit
,@TempK int
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
set error = '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