Robert,

Your solution works great ... I am however having a bit of a problem following the code - could you clarify the significance of the number 30 used in the WHERE clause?

Thanks a lot

DECLARE @max SMALLINT
SELECT @max = max(COlid) FROM syscomments 

SELECT DISTINCT O.Name, O.Type
    FROM
    (
        SELECT Id,
            CAST(COALESCE(MIN(CASE WHEN sc.colId = Num-1 THEN sc.text END), '') AS VARCHAR(8000)) +
            CAST(COALESCE(MIN(CASE WHEN sc.colId = Num THEN sc.text END), '') AS VARCHAR(8000)) AS [text]
        FROM syscomments SC
            INNER JOIN numbers N
                ON N.Num = SC.colid
                OR N.num-1 = SC.colid
        WHERE N.Num <= @max
        GROUP BY id, Num
    ) C
INNER JOIN sysobjects O
    ON C.id = O.Id
WHERE C.TEXT LIKE '%yourSearchString%'
 
SELECT DISTINCT O.Name, O.Type
    FROM
    (
        SELECT Id,
            CAST(COALESCE(MIN(CASE WHEN sc.colId = Num-1 THEN sc.text END), '') AS VARCHAR(8000)) +
            CAST(COALESCE(MIN(CASE WHEN sc.colId = Num THEN sc.text END), '') AS VARCHAR(8000)) AS [text]
        FROM syscomments SC
            INNER JOIN numbers N
                ON N.Num = SC.colid
                OR N.num-1 = SC.colid
        WHERE N.Num <= 255
        GROUP BY id, Num
    ) C
INNER JOIN sysobjects O
    ON C.id = O.Id
WHERE C.TEXT LIKE '%yourSearchString%'

SELECT DISTINCT O.Name, O.Type
 FROM
 (
 SELECT Id,
  CAST(COALESCE(MIN(CASE WHEN sc.colId = Num-1 THEN sc.text END), '') AS VARCHAR(8000)) +
  CAST(COALESCE(MIN(CASE WHEN sc.colId = Num THEN sc.text END), '') AS VARCHAR(8000)) AS [text]
FROM syscomments SC
 INNER JOIN (SELECT TOP 100 PERCENT ColID AS Num FROM Syscomments GROUP BY ColID ORDER BY ColID DESC) N
 ON N.Num = SC.colid
 OR N.num-1 = SC.colid
 GROUP BY id, Num
) C
INNER JOIN sysobjects O
 ON C.id = O.Id
 WHERE C.TEXT LIKE '%yourSearchString%'

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

Hi,

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

Thanks!