Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

BIT Barbarian

After working in the mortgage industry for 7 years, I transitioned into Business Intelligence and began learning SQL and .NET. My goal is to integrate my business knowledge into my development to intelligently analyze and find solutions to problems. Blogging offers me an option to share what I've learned as well as receive feedback on better practices and solutions.

Renaming all references inside stored procedures and functions can be migraine worthy without a little help…

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-13	sheldonh		@ 07:38:04: created 
*******************************************************/

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%''
					)

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	@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

set xact_abort off

 

Comments

Leave a comment on the original post [www.bitbarbarian.com, opens in a new window]

Loading comments...