Forgot your password?
typodupeerror
User Journal

joke_dst's Journal: SQL Server Search in stored procedures

Journal by joke_dst

A small script to search for a string in all stored procedures/triggers/functions etc. It outputs every occurance with some context (currently 50 chars before & after)
This searches for InFileID. Replace with something else.
This is not a stored procedure, because I don't want to create stored procedures in every database I need this functionality...

declare @pattern nvarchar(1000) set @pattern='%InFileID%'
declare @oid int set @oid=0
declare @oldOid int set @oldOid=-1
declare @text nvarchar(4000)
declare @index int
declare @relativeIndex int
declare @begindex int

while 1=1 begin
        select top 1 @oid=c.id, @text=c.text from sys.syscomments c where c.id>@oid and c.text like @pattern order by c.id
        if @oid=@oldOid break
        select o.Name, o.Type from sys.sysobjects o where o.id=@oid --Output name & type of proc/function/trigger

        set @index=0
        while 1=1 begin
                select @relativeIndex=patindex(@pattern, substring(@text, @index+1, 9999))
                if @relativeIndex=0 break
                set @index=@index+@relativeIndex
                set @begindex = (@index-50 + ABS(@index-50))/2 -- equals Max(0, @index-50)
                select @index inx, substring(@text, @begindex, 100) -- Output location and context of found occurance
        end

        set @oldOid=@oid
end

This discussion has been archived. No new comments can be posted.

SQL Server Search in stored procedures

Comments Filter:

"If it ain't broke, don't fix it." - Bert Lantz

Working...