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