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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s