Technical Article

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

Rate

5 (4)

Share

Share

Rate

5 (4)