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 stringtosearchCREATE FUNCTION findTextInCode (@StringToSearch varchar(100))RETURNS @fNames TABLE (search varchar(100), name sysname, type char(2))ASBEGIN 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 RETURNENDGO --Searches column names from stored procedure parameters and function,-- user tables, and view columnsCREATE FUNCTION findTextInColumnName (@StringToSearch varchar(100))RETURNS @fColumnNames TABLE (tablename sysname, colname sysname, type char(2))ASBEGIN 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 RETURNEND
Posts