Can this be done? Count instances of text in syscomments

Question

Can this be done? Count instances of text in syscomments

Rich Hansell

SSCrazy

Points: 2346
More actions
May 22, 2007 at 7:52 am

#172518

I am trying to find out the number of times a particular table is joined onto itself in our Stored procedures.. I can query syscomments and find the procs with the text ie '%join table%'.. but can I do a count of each occurrence in each proc? There has got to be an easy way
TIA,
Rich

Viewing 3 posts - 1 through 2 (of 2 total)

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

You'll need to be careful with this as some procs take up more than one row. The code below is almost and over-simplified solution to your problem. If your procedures are greater than 8000 characters in length the below will not work. Hopefully you'll find it a useful starting point....

DECLARE @toFind VARCHAR(100)

DECLARE @table TABLE (procName VARCHAR(200), procText varchar(8000))

SELECT @toFind = 'tblLog'

INSERT INTO @table

SELECT so.name, CASE WHEN colID = 1 THEN REPLACE(REPLACE(REPLACE(sc.text, CHAR(13) + CHAR(10), ' '), CHAR(9), ' '), ' ', ' ') END +

ISNULL(CASE WHEN colID = 2 THEN REPLACE(REPLACE(REPLACE(sc.text, CHAR(13) + CHAR(10), ' '), CHAR(9), ' '), ' ', ' ') END, '') +

ISNULL(CASE WHEN colID = 3 THEN REPLACE(REPLACE(REPLACE(sc.text, CHAR(13) + CHAR(10), ' '), CHAR(9), ' '), ' ', ' ') END, '')

FROM sysobjects so

INNER JOIN syscomments sc

ON so.id = sc.id

WHERE so.xtype = 'P'

AND CASE WHEN colID = 1 THEN REPLACE(REPLACE(REPLACE(sc.text, CHAR(13) + CHAR(10), ' '), CHAR(9), ' '), ' ', ' ') END +

ISNULL(CASE WHEN colID = 2 THEN REPLACE(REPLACE(REPLACE(sc.text, CHAR(13) + CHAR(10), ' '), CHAR(9), ' '), ' ', ' ') END, '') +

ISNULL(CASE WHEN colID = 3 THEN REPLACE(REPLACE(REPLACE(sc.text, CHAR(13) + CHAR(10), ' '), CHAR(9), ' '), ' ', ' ') END, '') IS NOT NULL

SELECT * FROM @table WHERE PATINDEX('%' + @toFind + '%', procText) > 0

SELECT procName, LEN(procText), LEN(REPLACE(procText, @toFind, '')),

(LEN(procText) - LEN(REPLACE(procText, @toFind, ''))) / LEN(@toFind)

FROM @table WHERE PATINDEX('%' + @toFind + '%', procText) > 0

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Rich Hansell SSCrazy Points: 2346 More actions · Answer 2

This is excellent.. I'll tweak it as needed. Thanks so much.

Rich