Searching Stored Procedures

Question

Post reply

Searching Stored Procedures

scking

SSCrazy

Points: 2598
More actions
February 26, 2004 at 9:38 am

#107096

I'm trying to build a procedure to search all stored procedures in the Db for a text string. What tables contain the text of the procedures and how to construct it. I get sysobject name but it doesn't have the text. Any assistance would be appreciated.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

The information you are looking for is in the syscomments table.

There are several scripts on this site that can get you started

For example try this one and modifiy it just for your paticular case

* Noel

scking SSCrazy Points: 2598 More actions · Answer 2

Thanks. That recommended T-SQL script does a great job. I'll create another script based on it to display the text.

noeld SSC Guru Points: 96590 More actions · Answer 3

noeld

SSC Guru

Points: 96590

February 26, 2004 at 4:16 pm

#496044

Glad I helped

* Noel

MikeMcDonald Say Hey Kid Points: 663 More actions · Answer 4

Better technique may be to use Enterprise Manager to create a script which generates all stored procedures. Save this file, then use standard searching methods. When text is stored in syscomments, are the statements broken at word boundaries? or anywhere? - in which case a search based on syscomments may fail to find the desired text.

fperkins Ten Centuries Points: 1029 More actions · Answer 5

sp_helptext 'proc name' returns text of proc doesn't it?

MikeMcDonald Say Hey Kid Points: 663 More actions · Answer 6

Yes, but the question asked was to find a nominated string in ALL procedures.

Coach James Ten Centuries Points: 1272 More actions · Answer 7

Old School:

loop through all SP's

exec sp_helptext 'proc name'

search text

break (when found)

end

Regards,

Coach James

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 8

This script also does the same functionality

http://www.sqlservercentral.com/scripts/contributions/1120.asp

However Greg has pointed out that it could be done with out using a cursor with the following script

create procedure myproc @mysearch varchar(100)

AS

SELECT sysobjects.name,

syscomments.text

FROM

sysobjects,syscomments

WHERE

sysobjects.id = syscomments.id

AND

sysobjects.type = 'P'

AND

sysobjects.category=0

and charindex(@mysearch,syscomments.text)>0

Which is also posted in the scripts section but yet to be published.

Thanks

Prasad Bhogadi
www.inforaise.com

scking SSCrazy Points: 2598 More actions · Answer 9

Thanks for the responses. I've implemented the following script based on some of the answers. This script outputs exactly what I need and I generally use it in Query Analyzer. The second, optional, parameter allows you to determine the number of hits which would cause the return of the sp name.

CREATE PROCEDURE spFindTextInObjects (@findText nvarchar(1000),

@havingCountGreaterThan smallint

= 0)

AS

-- Example Calls

-- EXECUTE spFindTextInObjects 'UNION ALL', 1

-- EXECUTE spFindTextInObjects 'XML EXPLICIT'

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @findText2 nvarchar(1002)

IF @findText IS NULL

RETURN

SET @findText2 = '%' + UPPER(@findText) + '%'

SELECT COUNT(*) As [FindCount],

OBJECT_NAME([id]) As [Name],

[id]

FROM syscomments

WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0

AND PATINDEX(@findText2, UPPER([text])) > 0

GROUP BY [id]

HAVING (COUNT(*) > @havingCountGreaterThan)

ORDER BY [FindCount] DESC,

[Name]

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

RETURN

okie_greg SSC Eights! Points: 911 More actions · Answer 10

I refined this as follows to make the result more meaningful...

create procedure myproc @mysearch varchar(100)

AS

SELECT sysobjects.name,

substring(syscomments.text,charindex(@mysearch,syscomments.text) - 10, 255)

FROM

sysobjects,syscomments

WHERE

sysobjects.id = syscomments.id

AND

sysobjects.type = 'P'

AND

sysobjects.category=0

and charindex(@mysearch,syscomments.text)>0

Richard_Hadfield Ten Centuries Points: 1113 More actions · Answer 11

I have tried to write something similar, however the reason for writing our proc was to search for those statements that change the database schema, so using the above examples would return all stored procedures if I use a search string of 'CREATE'.

So I modified the charindex/patindex value to start after the first find... this works but then there were comments stating the word CREATE ...

One of the solutions I have used is to strip the procedure prior to searching of any single or block comments but now having some very strange readings from syscomments

I am now going to move back to sp_helptext but as the maximum size of any stored procedure is 128 MB, how do you capture the whole body correctly certainly not with Varchar(8000) so my point is, is it possiblt to write a generic search function for user defined procedures???!

hoo-t SSChampion Points: 12713 More actions · Answer 12

Load the results from sp_helptext into a temporary table, one record per line of text.

Steve