Find a string in compiled objects

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]
fromsys.databases with (nolock)
wherestate_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
whereid = @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.objectsas SO with (nolock)
inner join[' + @database_name + '].sys.syscommentsas 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
selectdatabase_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
whereid = @database_counter

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




select
@object_counter = min( id )
,@object_limit = max( id )
from#_usf_concat_strings
wheredatabase_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
wheredatabase_name = @database_name
andid = @object_counter


select
@column_counter = min( column_id )
,@lines_of_code = max( column_id )
,@text = ''
from#_usf_objects_strings
wheredatabase_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
andcolumn_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
setconcat_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
whereconcat_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:

execdbo.usp_util_string_find
@input_string = ''MSrepl''
,@database_name = ''master''


Result:
database_nameobject_typeobject_nameinput_string
masterSQL_STORED_PROCEDUREsp_MSrepl_startupMSrepl
'
return @@error

end -- [usp_util_string_find]