The SQL Server 2000 implementation of INFORMATION_SCHEMA.ROUTINES only contains the first 4000 characters of the procedure definition (apparently this is fixed in SQL Server 2005). This makes it difficult to search for strings in the procedure text. If you search in sysobjects and syscomments instead, there will be multiple rows per procedure called, one for every 4000 characters of the procedure definition. At least this makes the text accessible. Here is an SQL statement that will search the definitions of functions, procedures, views, and triggers for a string (provided the string is no more than 50 characters long):
select so.name
from sysobjects so
inner join syscomments as sc on (sc.id=so.id)
left join syscomments as overlap on (overlap.id=so.id and overlap.colid=sc.colid+1)
where so.type in ('FN','IF','P','TF','TR','V')
and sc.text+coalesce(substring(overlap.text,1,50),'') like '%mystring%'Note the neat trick of self-joining syscomments to the next row in sequence to give 50 characters worth of overlap in case the search string spans rows (adjust the 50 to taste).
This is way easier than some of the expert advice about this topic on the 'Net.
