Searching Syscomments Accurately

Question

Post reply

Searching Syscomments Accurately

Viewing 10 posts - 16 through 24 (of 24 total)

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

Jeremy Hoy-369361 SSC Rookie Points: 27 More actions · Answer 1

I think this also works.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%some_text%'

AND ROUTINE_TYPE='PROCEDURE'

Michael Lysons SSCertifiable Points: 6491 More actions · Answer 2

Yep, that's what I was going to do, just not got round to it yet

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That will not work. This returns only the first row of text in syscomments (2000). No idea about 2005.

Jags2001 SSCommitted Points: 1529 More actions · Answer 4

Hi,

In your code you use a lot of GROUP BY's --- can you explain why?

Thanks!

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 5

Sure! Specifically what code are you refering to? The code in the article or the code in the forum (or both)

SQL guy and Houston Magician

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 6

I modified your last query a bit and obtained something that seems to be faster on SQL Server 2000:

SELECT distinct O.Name, O.Type

FROM

(

select

s1.id,

cast(coalesce(s1.text, '') as varchar(8000)) +

cast(coalesce(s2.text, '') as varchar(8000))

as [text]

from syscomments s1 left join syscomments s2

on s2.id = s1.id and s2.colid = s1.colid + 1

) C

INNER JOIN sysobjects O ON C.id = O.Id

WHERE C.TEXT LIKE '%yourSearchString%'

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 7

Wow! That's much better! Nice work Jesper!

SQL guy and Houston Magician

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 8

It looks like some of those group bys are unneccessary. Check out Jesper's code for the best 2k approach to date. As far as an explanation goes, This code evolved quite a bit, I never reviewed the code as a whole prior to posting.

In fact, the 2k code was based on the same approach used in the 2k5 code.

SQL guy and Houston Magician

dtalsma Valued Member Points: 66 More actions · Answer 9

I recently was examining source code and used a different technique. I believe the following will accomplish the same thing in SQL 2005. I do not know if it works in SQL 2000.

SELECT OBJECT_NAME(object_ID) AS Name, definition

FROM sys.sql_modules

WHERE definition LIKE '%YourSearchString%'

Jason Hannas SSC Enthusiast Points: 100 More actions · Answer 10

I was searching for the 2005 replace for syscomments and found this thread. I see it's fairly old, but figured I'd post the solution I've just worked up this afternoon. It seems to be working out well for me--fast and accurate as far as I tell.

You will need the use of a Numbers/Tally table:



CREATE PROCEDURE admin.pFindStringInCode

   @StringToFind nvarchar(50)

AS

SET NOCOUNT ON;



  SELECT  

    s.[name] AS SchemaName,

    o.[name] AS ObjectName,

    CAST(DENSE_RANK() OVER (ORDER BY s.[Name], o.[Name]) AS varchar) + '.' + 

    CAST(ROW_NUMBER() OVER (PARTITION BY s.[Name], o.[Name] ORDER BY s.[Name], o.[Name]) AS varchar) AS Instance,

    '...'+SUBSTRING(m.definition, CHARINDEX(@StringToFind, m.definition, n.Number), 100)+'...' AS Snippet 

  FROM

    sys.sql_modules AS m

    JOIN sys.objects AS o ON o.[object_id] = m.[object_id]

    JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]

    CROSS JOIN admin.tbl_Numbers AS n

  WHERE 

   n.Number < LEN(m.definition) 

   AND SUBSTRING(m.definition, n.Number, LEN(@StringToFind)) = @StringToFind

   AND m.definition LIKE N'%'+ @StringToFind + N'%'

   

SET NOCOUNT OFF;

GO