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)ASBEGIN 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 RETURNENDGO
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)ASBEGIN SET @StringToSEarch = '%' + @StringToSearch + '%' INSERT @fColumnNames SELECT DISTINCT SC.NAME FROM syscolumns SC (NOLOCK) WHERE SC.NAME LIKE @StringToSearch ORDER BY SC.Name RETURNENDGO
Some example usage for the novices:
select * from Find_Text_In_SP_or_FN('addr')GOSelect * from Find_Text_In_Column_Name('city')GOSelect * from Find_Text_In_Column_Name('state')GO
Posts