Author Message Tim Chapman-218780 SSC-Addicted Group: General Forum Members Points: 497 Visits: 115 Maybe...but its probably negligible. If you use sys.sql_modules, you still have to go lookup the name of the object.Tim R Michael SSCommitted Group: General Forum Members Points: 1559 Visits: 275 Thank you to everyone who has commented on the article. I've really enjoyed hearing how others have approached this problem and it's also great to hear that there are better approaches available in SQL2k. I've learned a lot from you guys! SQL guy and Houston Magician sql_er Ten Centuries Group: General Forum Members Points: 1427 Visits: 562 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 R Michael SSCommitted Group: General Forum Members Points: 1559 Visits: 275 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 SMALLINTSELECT @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 ) CINNER JOIN sysobjects O ON C.id = O.IdWHERE 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 ) CINNER JOIN sysobjects O ON C.id = O.IdWHERE 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) CINNER 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 SQL guy and Houston Magician Ramseier Andreas SSC-Enthusiastic Group: General Forum Members Points: 106 Visits: 90 This is also a good solution from Tim Chapman. He is using object_definition() function.http://www.sqlservercentral.com/articles/articlelink.asp?articleid=2809Andy Jeremy Hoy-369361 Forum Newbie Group: General Forum Members Points: 5 Visits: 8 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 SSCrazy Group: General Forum Members Points: 2698 Visits: 1472 Yep, that's what I was going to do, just not got round to it yet Ninja's_RGR'us SSC Guru Group: General Forum Members Points: 104359 Visits: 9671 That will not work. This returns only the first row of text in syscomments (2000). No idea about 2005. Jags2001 SSC-Addicted Group: General Forum Members Points: 455 Visits: 128 Hi,In your code you use a lot of GROUP BY's --- can you explain why?Thanks! R Michael SSCommitted Group: General Forum Members Points: 1559 Visits: 275 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