The 30 is an arbitary max of colid. If you have a proc greater than 120,000 bytes long, this will fail. Thank you for brining this up.

I've thought about it and there are three options:

1. Lookup the max colid value from syscomments before running the query (although this seems to kill the query plan)

2. Use the maximum possible value (255)

3. Skip the numbers table and join syscomments to itself to generate your range of colId Values.

Here is the code:

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

Overall, the last query--with the self-join--appears to be the fastest; it has fewer scans but more reads.

I hope this all helps