Marius Els (7/8/2008)
i use this to find text in any db object:
Hi Marius,
Mostly good, solid code, but there are a few caveats:
Select distinct sysobjects.id, name, type
from syscomments, sysobjects
where syscomments.id = sysobjects.id
and ( text like '%' + @criteria + '%' OR text like @criteria + '%' )
order by name
Using syscomments and sysobjects is fine for SQL Server 2000, but they are deprecated since SQL Server 2005 introduced the new system views.
One (minor) problem with this code is that syscomments stores a stored procedure text in chunks of 8000 characters. If the word you are searching for is just on the border of two chunks, it'll be cut in halve and this code won't find it. The chances of that are, of course, prettty slim 🙂
Finally, you can simplify the fourth line in the query to "and text like '%' + @criteria + '%'", snice the second part (searching for @criteria + '@') will never match rows not matched by the first part.
--check for dependencies.
exec sp_depends @criteria
This is fine when you are looking for table, view, function, or stored procedure names (though sp_depends to be less than accurate under some circumstances), but not when you are looking for procedures that use specific keywords (such as CURSOR).