I have now included views in the search code version. Both functions now return the object type the result was found in.
--Searches stored procedures, functions and views for code
-- containing the stringtosearch
CREATE FUNCTION findTextInCode (@StringToSearch varchar(100))
RETURNS @fNames TABLE (search varchar(100), name sysname, type char(2))
AS
BEGIN
SET @StringToSearch = '%' + @StringToSearch +'%'
INSERT @fNames
SELECT DISTINCT @stringtosearch, SO.Name, SO.Type
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.ID = SC.ID
AND (SO.Type = 'P' OR SO.Type LIKE '%F%' OR SO.Type = 'V')
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
RETURN
END
GO
--Searches column names from stored procedure parameters and function,
-- user tables, and view columns
CREATE FUNCTION findTextInColumnName (@StringToSearch varchar(100))
RETURNS @fColumnNames TABLE (tablename sysname, colname sysname, type char(2))
AS
BEGIN
SET @StringToSEarch = '%' + @StringToSearch + '%'
INSERT @fColumnNames
SELECT DISTINCT SO.NAME, SC.NAME, SO.TYPE
FROM syscolumns SC (NOLOCK)
INNER JOIN
sysobjects SO (NOLOCK)
ON SC.id = SO.id
WHERE SC.NAME LIKE @StringToSearch
ORDER BY SC.Name
RETURN
END