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]
