Find a string in compiled objects

,

This utility will search for strings in procedures, functions, views, and even check constraints in just one database or across all online databases on the server. It uses SQL Server 2005 system views and takes advantage of varchar(max) allowing us to concatenate all of the text (definition)columns for each object into a single, comparable string. This overcomes the problem (in SQL Server 2000) of a given string being split across two adjacent text rows and not being able to join them easily. At the end of the procedure, these concatenated values are used in a "like" comparison with the input string to return all instances in your compiled code. Don't worry about underscore separated words in your search string: underscores are properly escaped and accounted for in the comparison. Execute without parameters for usage. If you feel like tricking it out, you can use the output_print and testing parameters to get feedback during development.

Enjoy!

CREATE PROCEDURE dbo.usp_util_string_find
(
	@input_string varchar(255) = null
	,@database_name sysname = ''
	,@output_print int = 0
	,@testing int = 0
)
/*

exec dbo.usp_util_string_find
		@input_string = 'util_string_find'
		,@database_name = ''
		,@output_print = 1
		,@testing = 1

*/
/*_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_

	DECLARE_VARIABLES
	CREATE_TEMP_TABLES
	VALIDATE_INPUT
	LOAD_TEXT_FOR_ALL_DATABASE_OBJECTS

	INSERT_OBJECT_KEYS

	LOOP_OVER_DATABASES_OBJECTS_AND_ROWS
		OUTER_I_LOOP_OVER_DATABASES
			MIDDLE_J_LOOP_OVER_OBJECTS
				INNER_K_LOOP_OVER_DEFINITIONS

	RETURN_RECORD_SET

	HANDLE_ERRORS


_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_TABLE_OF_CONTENTS_*/
as
set nocount on
begin

	/**************************************************************************************************
		DECLARE_VARIABLES
	***************************************************************************************************/
	declare @count int

	declare @database_counter int
	declare @object_counter int
	declare @column_counter int

	declare @database_count int
	declare @object_limit int
	declare @object_id int

	declare @lines_of_code int

	declare @text varchar(max)

	declare @sql_string varchar(8000)
	declare @expression_string varchar(255)

	declare @activity varchar(255)
	declare @error_number int
	declare @error_severity int
	declare @error_state int
	declare @error_line int
	declare @error_message nvarchar(2048)



	/**************************************************************************************************
		CREATE_TEMP_TABLES
	***************************************************************************************************/
	declare @_usf_online_databases table (
		id int identity
		,database_name sysname
	)

	declare @_usf_target_databases table (
		id int identity
		,database_name sysname
	)

	create table #_usf_objects_strings (
		id int identity
		,database_name sysname
		,[object_id] int
		,object_type sysname
		,[object_name] sysname
		,column_id int
		,column_text nvarchar(4000)
	)

	create table #_usf_concat_strings (
		id int identity
		,database_name sysname
		,[object_id] int
		,object_type sysname
		,[object_name] sysname
		,concat_string varchar(max) default('')
	)




	/**************************************************************************************************
		VALIDATE_INPUT
	***************************************************************************************************/
	if coalesce( @input_string, '' ) = ''
	begin
		goto usage
	end

	--declare @expression_string varchar(255),@database_name sysname,@testing int;select @expression_string = 'T_WCV_MAX_SS_REF',@database_name = '',@testing = 0; set @expression_string = replace( @expression_string, '_', '[_]' );print @expression_string
	set @expression_string = '%' + replace( @input_string, '_', '[_]' ) + '%'


	if coalesce( @database_name, '' ) <> ''
	begin
		set @sql_string =
'
	if not exists ( select * from sys.databases where name = ''' + @database_name + ''' )
	begin
		insert	#_usf_objects_strings( database_name, [object_id], object_type, [object_name], column_id, column_text )
		select	''invalid'', ''0'', ''invalid'', ''invalid'', ''0'', ''invalid''
	end
'
		if @testing = 1
		begin
			print @sql_string
		end
		else
		begin
			execute ( @sql_string )
			if exists ( select * from #_usf_objects_strings where database_name = 'invalid' )
			begin
				print 'database not found in catalog'
				goto usage
			end
		end
	end
		


	if coalesce( @database_name, '' ) <> ''
	begin
		begin try
			set @activity = 'insert into @_usf_online_databases: ' + @database_name
			insert	@_usf_online_databases ( database_name )
			select	@database_name
		end try
		begin catch
			goto if_error
		end catch
	end
	else
	begin
		begin try
			set @activity = 'insert into @_usf_online_databases'
			insert into @_usf_online_databases ( database_name )
			select	[name]
			from	sys.databases with (nolock)
			where	state_desc = 'ONLINE'
		end try
		begin catch
			goto if_error
		end catch
	end





	/**************************************************************************************************
		LOAD_TEXT_FOR_ALL_DATABASE_OBJECTS
	***************************************************************************************************/
	select
		@database_counter = 1
		,@database_count = count(*)
	from	@_usf_online_databases


	while @database_counter <= @database_count
	begin
		set @database_name = ''

		select	@database_name = database_name
		from	@_usf_online_databases
		where	id = @database_counter

		if @output_print = 1
		print @database_name


		-- declare @activity varchar(255), @database_counter int, @database_name sysname, @testing int, @sql_string varchar(4000);select @database_counter = 11, @database_name = 'SLOB', @testing = 0
		set @sql_string = ''
	
		set @sql_string = 'insert into #_usf_objects_strings ( database_name, [object_id], object_type, [object_name], column_id, column_text ) ' + char(13)
		set @sql_string = @sql_string + 
'
	select	
		database_name = ''' + @database_name + '''
		,[object_id] = SO.[object_id]
		,object_type = SO.TYPE_DESC
		,[object_name] = SO.[name]
		,column_id = SC.colid
		,column_text = SC.[text]

	from	[' + @database_name + '].sys.objects		as SO with (nolock)
	inner join	[' + @database_name + '].sys.syscomments	as SC with (nolock) on SC.id = SO.object_id
'
		-- execute ( @sql_string )


		if @testing = 1
		begin
			print @sql_string
		end
		else
		begin
			begin try
				set @activity = 'execute ( @sql_string ): insert #_usf_objects_strings'
				execute ( @sql_string )
			end try
			begin catch
				goto if_error
			end catch
		end

		select @database_counter = @database_counter + 1
	end


	create clustered index ix_#_usf_objects_strings
	on #_usf_objects_strings ( database_name, [object_id], column_id )

	create index ix_#_usf_objects_strings__id
	on #_usf_objects_strings ( id )
	include( database_name, [object_id], column_id )



	/**************************************************************************************************
		INSERT_OBJECT_KEYS
			Object definitions will be concatenated later.
	***************************************************************************************************/
	insert into #_usf_concat_strings (
		database_name
		,[object_id]
		,object_type
		,[object_name]
	)
	select
		database_name
		,[object_id]
		,object_type
		,[object_name]
	from	#_usf_objects_strings
	group by
		database_name
		,[object_id]
		,object_type
		,[object_name]

	create unique clustered index ix_#_usf_concat_strings
	on #_usf_concat_strings ( database_name, [object_id] )






	/**************************************************************************************************
	/**************************************************************************************************
		LOOP_OVER_DATABASES_OBJECTS_AND_ROWS
	***************************************************************************************************/
	***************************************************************************************************/
	/**************************************************************************************************
		OUTER_I_LOOP_OVER_DATABASES
	***************************************************************************************************/
	insert into @_usf_target_databases
	select	database_name
	from	#_usf_concat_strings
	group by database_name
	set @database_count = @@rowcount

	set @database_counter = 1

	while @database_counter <= @database_count
	begin
		select	@database_name = database_name
		from	@_usf_target_databases
		where	id = @database_counter

		if @output_print = 1
		print '(((((((((((( DATABASE )))))))))))) -- ' + @database_name




		select
			@object_counter = min( id )
			,@object_limit = max( id )
		from	#_usf_concat_strings
		where	database_name = @database_name

		if @output_print = 1
		print '@@@@@@@@@@@@@@  OBJECT LIMIT  @@@@@@@@@@@@@@ -- ' + cast( @object_limit as varchar )

		/**************************************************************************************************
			MIDDLE_J_LOOP_OVER_OBJECTS
		***************************************************************************************************/
		while @object_counter <= @object_limit
		begin

			select	@object_id = [object_id]
			from	#_usf_concat_strings
			where	database_name = @database_name
			and	id = @object_counter


			select
				@column_counter = min( column_id )
				,@lines_of_code = max( column_id )
				,@text = ''
			from	#_usf_objects_strings
			where	database_name = @database_name
			and	[object_id] = @object_id



			if @output_print = 1
			begin
				print '*********OBJECT ID********* -- ' + cast( @object_id as varchar )
				print '##_##_##_##_##_##_##_##_##_ MAXIMUM COLUMN ID ##_##_##_##_##_##_##_##_##_ -- ' + cast( @lines_of_code as varchar )
				print '--------- JAY ID --------- -- ' + cast( @object_counter as varchar )
			end


			/**************************************************************************************************
				INNER_K_LOOP_OVER_DEFINITIONS
					Concatenate text column on each object.
			***************************************************************************************************/
			while @column_counter <= @lines_of_code
			begin
				if @output_print = 1
				print '--------- KAY ID --------- -- ' + cast( @column_counter as varchar )

				begin try
					set @activity = 'concatenate @text'
					select
						@text = @text + column_text
					from	#_usf_objects_strings
					where
						database_name = @database_name
					and	[object_id] = @object_id
					and	column_id = @column_counter
				end try
				begin catch
					goto if_error
				end catch

				set @column_counter = @column_counter + 1

			end -- while @column_counter <= @lines_of_code



			begin try
				set @activity = 'update #_usf_concat_strings.concat_string'
				update	#_usf_concat_strings
				set	concat_string = @text
				where
					database_name = @database_name
				and	[object_id] = @object_id
			end try
			begin catch
				goto if_error
			end catch

			set @object_counter = @object_counter + 1

		end -- while @object_counter <= @object_limit

		set @database_counter = @database_counter + 1

	end -- while @database_counter <= @database_count


	
	/**************************************************************************************************
		RETURN_RECORD_SET
	***************************************************************************************************/
	select
		database_name
		,[object_type]
		,[object_name]
		,input_string = @input_string
	from	#_usf_concat_strings
	where	concat_string like @expression_string
	order by
		database_name
		,[object_type]
		,[object_name]



	return @@error

	/**************************************************************************************************
		HANDLE_ERRORS
	***************************************************************************************************/
	if_error:
	select 
		@error_number		= coalesce( @error_number, ERROR_NUMBER(), 0 )
		,@error_severity	= coalesce( @error_severity, ERROR_SEVERITY(), 0 )
		,@error_state		= coalesce( @error_state, ERROR_STATE(), 0 )
		,@error_line		= coalesce( ERROR_LINE(), 0 )
		,@error_message		= coalesce( ERROR_MESSAGE(), 'empty ERROR_MESSAGE()' )

	select @error_message = 'Error::' + @activity + '::' + @error_message
	raiserror ( @error_message, @error_severity, @error_state ) with nowait
	return @error_number


usage:
print '
Purpose:	List all databases and objects in which the string is found.

Usage:
	Syntax:
		EXEC dbo.usp_util_string_find
			@input_string  = < string >
			,@database_name      = < null | database_name >

	Arguments:
		@input_string:	String input up to 255 characters.

		@database_name:	Search a particular database on the server. Null default will search all ONLINE databases.


Return values:
	A record set containing database name, type of object, object name, and user input string.

	Example:

		exec	dbo.usp_util_string_find
				@input_string = ''MSrepl''
				,@database_name = ''master''


		Result:
			database_name	object_type	object_name	input_string
			master	SQL_STORED_PROCEDURE	sp_MSrepl_startup	MSrepl
'
return @@error

end -- [usp_util_string_find]

Rate

5 (4)

Share

Share

Rate

5 (4)