Technical Article

Find strings in text of stored procedures

,

Ussualy this kind of scripts queries sysobjects and syscomments like this:

select distinct object_name(O.id)
from syscomments as C
join sysobjects as O on O.id = C.id
and o.type = 'P' -- search string only in stored procedures
where C.text like '%insert into MyTable%'


When the text of stored procedures, triggers, functions is over 4000 characters, it is
saved in syscomments on many records. The string I want to find may be splitted in 2
records:

......................insert in
to MyTable.....................

In this case the upper query will not work.

This script creates a temporary table with a [text] column where the entire text of stored
procedure is saved. It applies LIKE on that column and in this way no result is lost.
It also shows the number of occurrences of the string.

/*
Create by LiviuB, 2004-08-25
Description: the stored procedures looks for a specific string in the text of stored procedures, triggers, functions, etc.
It also shows the number of occurrences of the string in each object
Parameters:
 - @strSearchedString: the searched string
*/declare @strSearchedString nvarchar(4000)

/************START PARAMETERS *******************/set @strSearchedString = 'insert into Customer'
/************END PARAMETERS*******************/
set nocount on
declare @intI int, @intN int
declare @intI_txt int, @intN_txt int
declare @bintObjectID bigint
declare @strTxt nvarchar(4000)
declare @ptrval binary(16)
declare @intPosition int, @intID int, @sintNrOccurrences smallint

if object_id('tempdb..#tblObjects') is not null
drop table #tblObjects

create table #tblObjects(
  ID bigint identity(1, 1) 
, ObjectID bigint not null
, ObjectName varchar(100) not null
, RowsNr tinyint not null
, txt ntext not null
, NrOccurrences smallint not null default (0)
)

insert into #tblObjects(ObjectID, ObjectName, RowsNr, txt)
select C.id, object_name(C.id), count(C.text), ''
from syscomments as C
join sysobjects as O on O.id = C.id
and o.type = 'P'
-- ATTENTION !!! you can modify here to search other types of objects. You can use also "in ('P', 'FN', 'TF', 'IF')"
group by C.id

alter table #tblObjects add constraint PK_Objects primary key (ObjectID)

declare curObj cursor 
for
select ObjectID from #tblObjects

open curObj

fetch next from curObj into @bintObjectID
while @@fetch_status = 0
begin
select @intI_txt = 1, @intN_txt = RowsNr, @ptrval = TEXTPTR(txt), @strTxt = '' from #tblObjects where ObjectID = @bintObjectID
while @intI_txt <= @intN_txt
begin
select @strTxt = [text] from syscomments where id = @bintObjectID and colid = @intI_txt
updatetext #tblObjects.txt @ptrval null 0 @strTxt -- append the text

set @intI_txt = @intI_txt + 1
end

fetch next from curObj into @bintObjectID
end

close curObj

delete from #tblObjects
where txt not like ('%' + @strSearchedString + '%')

open curObj

fetch next from curObj into @bintObjectID
while @@fetch_status = 0
begin
set @sintNrOccurrences = 0
select @ptrval = TEXTPTR(txt)from #tblObjects where ObjectID = @bintObjectID

LABEL_BEGIN:
set @intPosition = 0
select @intPosition = PatIndex('%' + @strSearchedString + '%', txt)
from #tblObjects
where ObjectID = @bintObjectID


if @intPosition > 0
begin
set @intPosition = @intPosition + (datalength(@strSearchedString) / 2) - 1
UPDATETEXT #tblObjects.txt @ptrval 0 @intPosition N''
set @sintNrOccurrences = @sintNrOccurrences + 1
goto LABEL_BEGIN
end

if @sintNrOccurrences > 0
begin
update #tblObjects set NrOccurrences = @sintNrOccurrences where ObjectID = @bintObjectID
end

fetch next from curObj into @bintObjectID
end

close curObj
deallocate curObj

select 
  tmp.ObjectName as Name
, o.xtype as Type
, tmp.NrOccurrences as NrOccurrences
from #tblObjects as tmp
join sysobjects as o on o.id = tmp.ObjectID
order by tmp.ObjectName

Rate

Share

Share

Rate