Tuesday, May 31, 2005

Searching Stored Procedures Source Code

I found this article while searching for some "text" in my own stored procedures, (the article link is available at the bottom.)
October 6, 2000
Searching Stored Procedures Source Code
By Alan Enderby

Want to find that stored procedure that deletes those stock items.

This simple (& crude) script will scan syscomments for a given string
and display the stored procedure and the section of code.

if exists (select * from sysobjects where id =
object_id('dbo.sp_findsp') and sysstat & 0xf = 4)
drop procedure dbo.sp_findsp

create proc sp_findsp @s varchar(255) as
DECLARE @msg varchar(255) ,@ul varchar(255)
select @s='%' + @s + '%'
select 'SP Name'=upper(o.name), Seq=colid ,'SP
Line'=substring(text,patindex(@s,text)-5, 30)
from syscomments c , sysobjects o
where o.id=c.id
and patindex(@s,text) > 0
order by name
SELECT @msg='* Stored procedures containing string "' + @s + '=' +
convert(varchar(8),@@rowcount) + ' *'
SELECT @ul=replicate('*',datalength(@msg))
Print ' '
PRINT @msg
Print @ul

Searching Stored Procedures Source Code

No comments:

Post a Comment