:::: MENU ::::

Search within Store Procedure Text !

Recently Needs to find  ‘/’ character in Sql store procedure body and replace with ‘\’

Now Having about 100 Procs and its really boring job to open each proc and have check for it ! .. so find out small handy sql query so that it will give me list of procs contains specific string .. Once list of proc is known .. one can simply edit them !

DECLARE @StringToSearch varchar(100)
SET @StringToSearch = ‘%’ + <STRING TO SEARCH> + ‘%’
SELECT Distinct sysobjects.Name
FROM sysobjects (NOLOCK)
INNER JOIN syscomments (NOLOCK) on sysobjects.Id = syscomments.ID
AND sysobjects.Type = ‘P’
AND syscomments.Text LIKE @stringtosearch
ORDER BY sysobjects.Name

Happy Coding !

DECLARE @StringToSearch varchar(100)
SET @StringToSearch = ‘%’ + <STRING TO SEARCH> + ‘%’
SELECT Distinct sysobjects.Name
FROM sysobjects (NOLOCK)
INNER JOIN syscomments (NOLOCK) on sysobjects.Id = syscomments.ID
AND sysobjects.Type = ‘P’
AND syscomments.Text LIKE @stringtosearch
ORDER BY sysobjects.Name

One Comment

So, what do you think ?