Search SQL Server for stored procedures in all databases containing text

Search SQL Server for stored procedures in all databases containing text

USE [master]
GO

CREATE PROCEDURE sp_SearchProcs  
(  
 @SearchText VARCHAR(1000)  
)  
AS  
BEGIN  
 DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR    
    SELECT [name]    
    FROM master..sysdatabases   
    WHERE [name] NOT IN ('model', 'tempdb')   
    ORDER BY [name]   
        
 DECLARE @DB sysname    
  
 OPEN curDB    
 FETCH NEXT FROM curDB INTO @DB    
 WHILE @@FETCH_STATUS = 0    
    BEGIN    
     DECLARE @SQL NVARCHAR(MAX) = 'USE [' + @DB +'] SELECT Distinct SO.Name, SC.Text, SS.Name AS [Schema], ''' + @DB + ''' AS [Database] FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID INNER JOIN sys.objects SO2 (NOLOCK) ON SO.id = SO2.object_id INNER JOIN sys.schemas SS ON SO2.schema_id = SS.schema_id WHERE SO.Type = ''P'' AND SC.Text LIKE ''%' + REPLACE(@SearchText, '''', '''''') + '%'' ORDER BY SO.Name'  
     BEGIN TRY  
       exec sp_executesql @SQL  
     END TRY  
     BEGIN CATCH  
     END CATCH  
     FETCH NEXT FROM curDB INTO @DB    
    END    
       
 CLOSE curDB    
 DEALLOCATE curDB  
END

Here's how to use it:

sp_SearchProcs 'insert%into%mytable'