Searching for a text on SQL Server (PL SQL Style)

Hi those coming from a PL SQL background will be knowing of a very common functionality which enables you to search for a particular text through PL SQL Dev tool on a particular text. Now if you try to look for a same type of a feature on SQL Server Management Studio, there is none.


The work around is the following stored procedure.
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%foobar%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

So if you want to search for a text like foobar in all procedures used in the database, the query would look like something above. If the same thing has to be searched in a function then the parameter "IsProcedure" has to be changed to "IsFunction".


This tip will particularly important if you are associated with support and enhancement kind of activity and need to find say "How many times a particular table name is used in a procedure or a function".