Searching Syscomments Accurately

  • I think this also works.

    SELECT ROUTINE_NAME, ROUTINE_DEFINITION

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_DEFINITION LIKE '%some_text%'

    AND ROUTINE_TYPE='PROCEDURE'

  • 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?

    Thanks!

     

  • 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

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

  • 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)
    
    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 varcharAS Instance,
    
        '...'+SUBSTRING(m.definitionCHARINDEX(@StringToFindm.definitionn.Number), 100)+'...' AS Snippet 
    
      FROM
    
        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
    
      WHERE 
    
       n.Number LEN(m.definition) 
    
       AND SUBSTRING(m.definitionn.NumberLEN(@StringToFind)) @StringToFind
    
       AND m.definition LIKE N'%'@StringToFind N'%'
    
       
    
    SET NOCOUNT OFF;
    
    GO

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

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