text: IF OBJECT_ID('dbo.usp_SearchStoredProcedureText', 'P') IS NULL
  EXECUTE ('CREATE PROCEDURE dbo.usp_SearchStoredProcedureText AS RETURN');
  GO
  
  
  ALTER PROCEDURE dbo.usp_SearchStoredProcedureText(
      @searchforthis NVARCHAR(512)
    , @object_type VARCHAR(2) = NULL
    , @is_ms_shipped BIT = 0
  )
  AS
  /*
  Author: Tim Ford
  Original link: https://www.itprotoday.com/microsoft-sql-server/using-sqlmodules-system-catalog-view-search-function-and-stored-procedure
  */
  SET NOCOUNT ON;
  
  DECLARE @search_text NVARCHAR(MAX);
  
  CREATE TABLE #search_results
      (
      the__database sysname NOT NULL,
      the__schema sysname NOT NULL,
      object__name sysname NOT NULL,
      object__type NVARCHAR(120) NOT NULL,
      is_ms_shipped BIT NOT NULL,
      sql__text NVARCHAR(MAX) NOT NULL
      );
  
  
  IF @object_type IS NULL
      BEGIN
          SELECT @search_text =
          'USE ?; 
          INSERT INTO #search_results (the__database, the__schema, object__name, object__type, is_ms_shipped, sql__text) 
          SELECT db_name() AS the__database
                  , OBJECT_SCHEMA_NAME(O.object_id) AS the__schema
                  , O.name AS object__name 
                  , O.type_desc AS object__type
                  , O.is_ms_shipped
                  , M.definition AS sql__text
          FROM sys.objects O WITH(NOLOCK)
                  LEFT JOIN sys.sql_modules M ON O.object_id = M.object_id
          WHERE O.is_ms_shipped = ' + CAST(@is_ms_shipped AS VARCHAR(1)) + ' AND M.definition LIKE ''%' + @searchforthis + '%''' + ';'
      END
  ELSE
      BEGIN
          SELECT @search_text =
          'USE ?; 
          INSERT INTO #search_results (the__database, the__schema, object__name, object__type, is_ms_shipped, sql__text) 
          SELECT db_name() AS the__database
                  , OBJECT_SCHEMA_NAME(O.object_id) AS the__schema
                  , O.name AS object__name 
                  , O.type_desc AS object__type
                  , O.is_ms_shipped
                  , M.definition AS sql__text
          FROM sys.objects O WITH(NOLOCK)
              LEFT JOIN sys.sql_modules M ON O.object_id = M.object_id
          WHERE O.is_ms_shipped = ' + CAST(@is_ms_shipped AS VARCHAR(1)) + ' AND O.type = ''' + @object_type + '''' +
              'AND M.definition LIKE ''%' + @searchforthis + '%''' + ';'
      END
  
  EXEC dbo.sp_foreachdb @command = @search_text;
  
  SELECT the__database
      , the__schema
      , object__name
          , object__type
          , is_ms_shipped
      , sql__text 
  FROM #search_results 
  ORDER BY the__database
      , the__schema
      , object__name;
  GO
  