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