SQL-Server-Tools/sp_FindInAllProcs.txt

CREATE PROCEDURE sp_FindInAllProcs
	@Text1 VARCHAR(200)='',
	@Text2 VARCHAR(200)=''
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Loop INT
	DECLARE @Count INT
	DECLARE @Database VARCHAR(200)
	DECLARE @SQL NVARCHAR(4000)
	DECLARE @Databases TABLE
	(
		[DatabaseID] INT IDENTITY(1,1),
		[Database] VARCHAR(200)
	)
	CREATE TABLE #Output
	(
		[Database] VARCHAR(200),
		[ProcName] VARCHAR(200),
		[Sample1] VARCHAR(200),
		[Sample2] VARCHAR(200)
	)

	IF LEN(ISNULL(@Text1,'')) = 0 AND LEN(ISNULL(@Text2,'')) = 0
	BEGIN
		DECLARE @Info TABLE
		(
			Info VARCHAR(100)
		)
		INSERT INTO @Info (Info) VALUES ('sp_FindInAllProcs text1,text2 (optional)')
		INSERT INTO @Info (Info) VALUES ('Searches all stored procedures in all databases for text1 and text2')
		INSERT INTO @Info (Info) VALUES ('Lists the database, stored procedure, and a sample of the found text')
		INSERT INTO @Info (Info) VALUES ('')
		INSERT INTO @Info (Info) VALUES ('Check out other cool tools like')
		INSERT INTO @Info (Info) VALUES ('sp_ColumnsInATable')
		INSERT INTO @Info (Info) VALUES ('sp_CreateUserDefinedType')
		INSERT INTO @Info (Info) VALUES ('sp_DropColumn')
		INSERT INTO @Info (Info) VALUES ('sp_Find')
		INSERT INTO @Info (Info) VALUES ('sp_FindColumn')
		INSERT INTO @Info (Info) VALUES ('sp_FindInProc')
		INSERT INTO @Info (Info) VALUES ('sp_List')		
		INSERT INTO @Info (Info) VALUES ('sp_varinsp')
		SELECT Info from @Info
		RETURN
	END
	
	INSERT INTO @Databases ([Database])
	SELECT name from sysdatabases where name not in ('master','tempdb','model','msdb')

	SET @Count = @@ROWCOUNT
	SET @Loop = 1
	WHILE @Loop <= @Count
	BEGIN
		SELECT @Database = [Database]
		FROM @Databases
		WHERE DatabaseID = @Loop
		
		IF LEN(ISNULL(@Text2,''))=0
		BEGIN
			SET @SQL = N'INSERT INTO #Output([Database],ProcName,Sample1,Sample2) SELECT ''' + @Database + ''',so.name,SUBSTRING(sc.text,CHARINDEX('''+@Text1+''',sc.text)-20,40),'''' FROM ' + @Database + '..sysobjects as so inner join ' + @Database + '..syscomments AS SC on so.id = sc.id WHERE so.xtype =''P'' AND sc.text like ''%'+@Text1+'%'''
		END ELSE
		BEGIN
			SET @SQL = N'INSERT INTO #Output([Database],ProcName,Sample1,Sample2) SELECT ''' + @Database + ''',so.name,SUBSTRING(sc.text,CHARINDEX('''+@Text1+''',sc.text)-20,40),SUBSTRING(sc.text,CHARINDEX('''+@Text2+''',sc.text)-20,40) as Sample2 FROM ' + @Database + '..sysobjects as so inner join ' + @Database + '..syscomments AS SC on so.id = sc.id WHERE so.xtype =''P'' AND sc.text like ''%'+@Text1+'%'' AND sc.text like ''%'+@Text2+'%'''
		END
		EXEC sp_sqlexec @SQL

		SET @Loop = @Loop + 1
	END

	IF LEN(ISNULL(@Text2,''))=0
	BEGIN
		SELECT [Database],ProcName,Sample1 as [Sample] FROM #Output ORDER BY [Database],ProcName
	END ELSE
	BEGIN
		SELECT [Database],ProcName,Sample1,Sample2 FROM #Output ORDER BY [Database],ProcName
	END

	DROP TABLE #Output
END
GO