Search module definitions

This script will search the text of SQL modules (SPs, functions, view definitions etc) for a specified string.

SearchModules.sql

DECLARE @searchText NVARCHAR(MAX) = 'your text here'
DECLARE @dbName NVARCHAR(128) = '%'
DECLARE @noEscape NVARCHAR(5) = ''
 
-- *****************************************
 
SET NOCOUNT ON 
 
DECLARE @escapeChar NVARCHAR(1) = '\'
DECLARE @i INT
 
/*
 * prepare list of characters to escape
 */
-- basic list
DECLARE @charsToEscape NVARCHAR(3) = '%_['
 
-- strip out characters NOT to be escaped
SET @i = 1
 
WHILE @i <= LEN(@noEscape)
BEGIN
  SET @charsToEscape = REPLACE (@charsToEscape, SUBSTRING(@noEscape, @i, 1), '')
  SET @i += 1
END
 
-- need to escape the escape character itself
SET @charsToEscape += @escapeChar
 
/*
 * prepare search
 */
-- prepare search string by escaping any characters in @charsToEscape
SET @i = 1
 
WHILE @i <= LEN(@charsToEscape)
BEGIN
  SET @searchText = 
    REPLACE (
      @searchText
    , SUBSTRING(@charsToEscape, @i, 1)
    , @escapeChar + SUBSTRING(@charsToEscape, @i, 1)
    )
  SET @i += 1
END
 
-- prepare search query
DECLARE @sql NVARCHAR(MAX) = 'SELECT
  DB_NAME()
, s.[name]
, o.[name]
, o.type_desc 
FROM sys.schemas s
  INNER JOIN sys.objects o ON o.schema_id = s.schema_id
  INNER JOIN sys.sql_modules c ON c.object_id = o.object_id
WHERE UPPER(c.[definition]) LIKE ''%'' + ''' + UPPER(REPLACE(@searchText, '''', '''''')) 
  + ''' + ''%'' ESCAPE ''' + @escapeChar + '''
AND o.[type] IN (
  ''V''  -- view
, ''P''  -- stored procedure
, ''FN'' -- scalar function
, ''TF'' -- table function
, ''IF'' -- inline table function
, ''TR'' -- trigger
, ''C''  -- CHECK constraint 
, ''D''  -- Default
, ''RF'' -- Replication filter stored procedure
)'
 
-- prepare table for search results
DECLARE @results TABLE (
  DbName NVARCHAR(128)
, SchemaName NVARCHAR(128)
, ObjectName NVARCHAR(128)
, ObjectType NVARCHAR(128)
)
 
/*
 * search
 */
DECLARE @db NVARCHAR(128)
DECLARE @dbSql NVARCHAR(MAX)
DECLARE @msg NVARCHAR(MAX) = ''
 
DECLARE dbCursor CURSOR LOCAL FOR
  SELECT 
    [name]
  FROM [master].sys.databases
  WHERE [name] LIKE @dbName
 
OPEN dbCursor 
FETCH NEXT FROM dbCursor INTO @db
 
WHILE @@FETCH_STATUS = 0 
BEGIN
 
  BEGIN TRY 
 
    SET @dbSql = 'EXEC [' + @db + '].dbo.sp_executesql N''' 
      + REPLACE(@sql, '''', '''''') + ''''
    INSERT INTO @results EXEC(@dbSql)
 
  END TRY
  BEGIN CATCH
 
    SET @msg += '
 - Error ' + CAST(ERROR_NUMBER() AS VARCHAR) 
      + ' searching [' + @db + ']: ' + ERROR_MESSAGE() 
 
  END CATCH
 
  FETCH NEXT FROM dbCursor INTO @db
 
END
 
CLOSE dbCursor 
DEALLOCATE dbCursor
 
/*
 * report results
 */
-- results of search
SELECT 
  DbName
, SchemaName 
, ObjectName
, ObjectType
FROM @results
ORDER BY 
  DbName
, SchemaName 
, ObjectName
 
-- any errors
IF LEN(@msg) > 0
BEGIN
 
  SET @msg = 'Search encountered the following errors:' + @msg
  RAISERROR(@msg, 11, 1)
  PRINT 'Any available search results are visible in the results pane.'
 
END

If you want to turn this into an SP, make the first three variables into parameters, with only the first mandatory:

CREATE PROCEDURE [dbo].[usp_SearchModules] (
  @searchText NVARCHAR(MAX) 
, @dbName NVARCHAR(128) = '%'
, @noEscape NVARCHAR(5) = ''
) 
AS
 
SET NOCOUNT ON 
-- ...