====== Search module definitions ======

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

<WRAP center round important 75%>Modules are matched on the basis of a SQL "''[definition] LIKE '%searchString%<nowiki>'</nowiki>''" operation, so the same identifier with and without delimiters is **different**.
</WRAP>


<file tsql 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</file>

===== Create an SP to do this =====
If you want to turn this into an SP, make the first three variables into parameters, with only the first mandatory:
<code tsql>CREATE PROCEDURE [dbo].[usp_SearchModules] (
  @searchText NVARCHAR(MAX) 
, @dbName NVARCHAR(128) = '%'
, @noEscape NVARCHAR(5) = ''
) 
AS

SET NOCOUNT ON 
-- ...
</code>


{{tag> admin tsql development dynamic}}