Oh my! Phew! "one cannot cavalierly cast the code text to varchar".
Ouch! Hehe.



-----Original Message-----
From: Edwards Ed
[mailto:mssqldba-ezmlmshield-x93884401.[Email address protected]
Sent: Sunday, September 30, 2007 10:26 AM
To: LazyDBA Discussion
Subject: RE: Find a spesific text from a Sp

I tell you yes! Hehe. That "pesky wannbe DBA but really is a
developer(Oh my! Phew!)" is always lurking in the shadows! Hehe. Just
maybe, just maybe, he or her will go on vacation and drink "Red Stripe"!
hehe. Anyway, check out the code below. Hehe.

Ed--

Couple of problems with your code...

If the proc author uses double-byte characters (e.g. comments),
obviously one cannot cavalierly cast the code text to varchar.

The syscomments.number column is used for naming and grouping stored
procedures, not for sequencing long definitions.

DECLARE @srch nvarchar(50)
SET @srch = N'project_name'
SELECT DISTINCT OBJECT_NAME(a.id)
FROM syscomments a LEFT JOIN syscomments b ON a.id = b.id AND a.colid =
b.colid - 1 WHERE a.text LIKE N'%' + @srch + N'%'
OR RIGHT(a.text,49) + LEFT(b.text,49) LIKE N'%' + @srch + N'%'


Have a great Day and Weekend! Phew! Phew! Phew! Hehe.






Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Jeanine Blomberg is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below. Thank you in
advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Edwards, Ed
Sent: Sunday, September 30, 2007 7:46 AM
To: 'Aradana Liyanage '; LazyDBA Discussion
Subject: RE: Find a spesific text from a Sp
Importance: High

Well, well, well,
It's Aradana Liyanage! Hehe. How have you been?

declare @srch varchar(50)
/* specify the searchstring here */
set @srch='project_name'
SELECT
Object_Name(c1.id) name,
substring(c1.text,(CHARINDEX(@srch,c1.text)-5),60) as text
FROM syscomments c1
LEFT OUTER JOIN syscomments c2 ON c1.id = c2.id AND c2.number =
c1.number + 1
WHERE Cast( c1.text AS varchar(4000) )+ Coalesce( Cast( c2.text AS
varchar(4000) ), '' )
LIKE '%'+@srch+'%'
/* add more search conditions here as desired */ ORDER BY 1


It will soon be NFL time! Hehe. Partyyyyyyy! Hehe.

Have a great Day and Weekend!



-----Original Message-----
From: Aradana Liyanage
[mailto:mssqldba-ezmlmshield-x94170326.[Email address protected]
Sent: Saturday, September 29, 2007 5:03 AM
To: LazyDBA Discussion
Subject: Find a spesific text from a Sp

Hi Gurus



I am using SQL 2000.

My web app uses Sql stored procedures. I just wanted to find a specific
stored procedure which contains some specific text



Eg : I wanted to find a sp which has a text "project_name" in it



Is this possible?



Aradhana





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page