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