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 
