Functions to find text in column names and in stored procedures and function definitions

Implemented in SQL Server:
This function finds a text string in a stored procedure or function definition:


CREATE FUNCTION Find_Text_In_SP_or_FN (@StringToSearch varchar(100))
RETURNS @fNames TABLE (name sysname)
AS
BEGIN
SET @StringToSearch = '%' + @StringToSearch +'%'
INSERT @fNames
SELECT DISTINCT SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.ID = SC.ID
AND (SO.Type = 'P' OR SO.Type LIKE '%F%')
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
RETURN
END
GO

This function finds a text string in a column name:


CREATE FUNCTION Find_Text_In_Column_Name (@StringToSearch varchar(100))
RETURNS @fColumnNames TABLE (colname sysname)
AS
BEGIN
SET @StringToSEarch = '%' + @StringToSearch + '%'
INSERT @fColumnNames
SELECT DISTINCT SC.NAME
FROM syscolumns SC (NOLOCK)
WHERE SC.NAME LIKE @StringToSearch
ORDER BY SC.Name
RETURN
END
GO

Some example usage for the novices:


select * from Find_Text_In_SP_or_FN('addr')
GO
Select * from Find_Text_In_Column_Name('city')
GO
Select * from Find_Text_In_Column_Name('state')
GO