Searching Syscomments Accurately

  • I think this also works.





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


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

  • Hi,

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



  • 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

  • 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




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

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

     as [text]

     from syscomments s1 left join syscomments s2

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

    ) C

    INNER JOIN sysobjects O ON = O.Id

    WHERE C.TEXT LIKE '%yourSearchString%'

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

    SQL guy and Houston Magician

  • 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

  • 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%'

  • 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)
        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 varcharAS Instance,
        '...'+SUBSTRING(m.definitionCHARINDEX(@StringToFindm.definitionn.Number), 100)+'...' AS Snippet 
        sys.sql_modules AS m
        JOIN sys.objects AS ON o.[object_id] m.[object_id]
        JOIN sys.schemas AS ON o.[schema_id] s.[schema_id]
        CROSS JOIN admin.tbl_Numbers AS n
       n.Number LEN(m.definition) 
       AND SUBSTRING(m.definitionn.NumberLEN(@StringToFind)) @StringToFind
       AND m.definition LIKE N'%'@StringToFind N'%'

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

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