Search Stored Procedures for Any Text

Sometimes you need to find all stored procedures in all databases in SQL Server that contain certain text. For example, today I had to find if any of our store procedures were connecting to other database servers via an IP address. I found the stored procedure below on the web and it works great.

-- exec sp_SearchText 
CREATE PROC SP_SEARCHTEXT 
    @search NVARCHAR(1000) 
AS 

CREATE TABLE #RESULTS ( 
    [DATABASE] NVARCHAR(128), 
    [SCHEMA] NVARCHAR(128), 
    [NAME] NVARCHAR(128), 
    [TYPE] NVARCHAR(20), 
    [CREATED] DATETIME, 
    [MODIFIED] DATETIME 
) 

DECLARE @db NVARCHAR(128) 
DECLARE @sql NVARCHAR(1000) 

DECLARE CURDATABASES CURSOR FOR 
    SELECT DISTINCT NAME FROM MASTER..SYSDATABASES 

OPEN CURDATABASES 
FETCH NEXT FROM CURDATABASES INTO @db 
WHILE @@FETCH_STATUS = 0 
    BEGIN 
        SET @sql = ' insert into #results select  ROUTINE_CATALOG [Database], ROUTINE_SCHEMA [Schema], 

                     ROUTINE_NAME [Name], ROUTINE_TYPE [Type], CREATED [Created], 

                     LAST_ALTERED [Altered] from  ['+ @db +'].INFORMATION_SCHEMA.ROUTINES 

                     where ROUTINE_DEFINITION like N''%' + @search + '%'' ' 

        EXEC(@sql) 

        FETCH NEXT FROM CURDATABASES INTO @db 
    END 
CLOSE CURDATABASES 
DEALLOCATE CURDATABASES 

SELECT * FROM #RESULTS 

GO 

To use, run the stored procedure in the master database and then searching like this:

sp_SearchText '10.0.0'

Tip Submitted By: David McCarter