October 13, 2004 at 5:38 pm
hi I have a very large database with several stored procedures. now, I need to find a stored procedure that does some calculation if that stored procedure has a string "MESSAGE"(just an example) in an IF statement somewhere down the procedure. currently i am opening every stored procedure, doing a Ctrl+F for the above string and if i find it then that means i determine that this is the stored procedure that i will have to work with(change the calculation in it or some work) so my question is that can i write something ... that will list me all the stored procedures which have a certain string contained in them?? I am new to sql stuff .. so please help me. this could save me a lot of time. thanks -messageman | |
October 13, 2004 at 7:09 pm
-- exec proc sp_Find_in_Proc('searchstring")
CREATE proc sp_Find_in_Proc
@SeachString varchar(255) = NULL
AS
IF @SeachString is Null
Begin
Print 'Missing Parameter @SeachString!'
return
end
DECLARE @Pattern as varchar(255)
SELECT @Pattern = '%"' + @SeachString + '%'
Print ' '
Print 'Seaching for ' + @Pattern + ' in procedures....'
Print ' '
Print ' Found in procedures...'
SELECT name FROM syscomments,sysobjects
WHERE OBJECTPROPERTY(sysobjects.id,N'IsProcedure')=1
And sysobjects.id = syscomments.id
AND PATINDEX(@Pattern , text) !=0
ORDER BY Name
October 15, 2004 at 7:48 am
We have been using:
-- sp_grep v1.0 03/16/1995, v1.1 10/26/1995,
-- Author: Andrew Zanevsky, AZ Databases, Inc.
-- E-mail: zanevsky@azdatabases.com
A co-worker found it at
http://www.sqlservercentral.com/scripts/contributions/1201.asp
It will find a string almost anywhere in the database - column names, stored procedures, triggers... Works great.
October 15, 2004 at 10:23 am
Try this query.
select distinct o.name
from syscomments c , sysobjects o
where c.text like '%base%' and
c.id = o.id and
o.xtype = 'p'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply