Find SMO in Stored Procs

This is a very handy procedure that will return all Views, Stored Procs, Functions and Triggers that contain the string being searched.

I have been working on a 2005 Management Studio addin that uses this procedure and returns the SMO Nodes in a treeview, which you can then double click to open the object to edit/view. Unfortunately it can't be installed into Query Analyser. I am hoping to be able to provide the code for this sometime soon.

-- Set the @searchtext param to the name of the object that you want
-- to find within code of a stored proc.
-- It performs a partial match on the search text by using the % property pre and post
DECLARE @searchtext varchar(50) -- the search text
DECLARE @sql varchar(256) --Building the SQL to run
DECLARE @SearchAllDBs bit --1 = Search All Databases, 0 = Search Current DB

-- Set this
SET @searchtext = ''
SET @SearchAllDBs = 1
if @SearchAllDBs = 0
begin
set @sql = 'SELECT name, type ' set @sql = @sql +
'FROM sysobjects a INNER JOIN syscomments b on a.id = b.id '
end
else
begin
set @sql = 'SELECT ''?'' ''Database'', name, type ' set @sql = @sql +
'FROM ?.dbo.sysobjects a INNER JOIN ?.dbo.syscomments b on a.id = b.id '
end

set @sql = @sql + 'WHERE type in (''U'',''IF'', ''P'', ''V'', ''FN'', ''TF'', ''TR'') '+
'AND b.text LIKE ''%' + @searchtext + '%'' '+
'ORDER BY type, name'

if @SearchAllDBs = 0
exec (@sql)
else
exec sp_MSforeachdb @sql