Catch up on stories from the past week (and beyond) at the Slashdot story archive


Forgot your password?
Check out the new SourceForge HTML5 internet speed test! No Flash necessary and runs on all devices. ×
User Journal

Journal joke_dst's Journal: SQL Server Search in stored procedures

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, @text=c.text from sys.syscomments c where>@oid and c.text like @pattern order by
        if @oid=@oldOid break
        select o.Name, o.Type from sys.sysobjects o where --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

        set @oldOid=@oid

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

SQL Server Search in stored procedures

Comments Filter:

In every hierarchy the cream rises until it sours. -- Dr. Laurence J. Peter