--modyfikuj na samym dole na samym dole

CREATE PROCEDURE dbo.#SearchProcsForName 
	@db SYSNAME, --name of database
	@word NVARCHAR(100) --searched word
AS

--Table stores all procedures from database
CREATE TABLE #procedure
([id] int primary key IDENTITY,
 [schema] NVARCHAR(1000),
 [name] NVARCHAR(1000))

--Take all procedures in database...
DECLARE @sql NVARCHAR(1000)
SET @sql= 'USE '+@db+';SELECT schema_Name(schema_id),name from '+@db+'.sys.procedures'

--... and store them in table 
INSERT #procedure
	EXEC sp_executesql @cmd=@sql	
	
--table stores all text lines from procedures
CREATE TABLE #proctext
(
id int primary key identity,
procid int,
text VARCHAR(8000))

DECLARE @procname SYSNAME
DECLARE @procid INT
DECLARE Cur CURSOR FOR  SELECT id, [schema]+'.'+[name] FROM #procedure
OPEN cur
FETCH NEXT FROM Cur INTO @procid,@procname
WHILE @@FETCH_STATUS =0
BEGIN

	SET @sql = 'USE '+@db+'; EXEC sp_helptext '''+@procname+''''
	INSERT #proctext(text)
		EXECUTE sp_executesql @cmd=@sql
	
	UPDATE #proctext SET procid=@procid WHERE procid IS NULL
	
	FETCH NEXT FROM cur INTO @procid, @procname
END

CLOSE cur
DEALLOCATE cur

--select * from #proctext

--select * from #procedure
SELECT @db As 'DBNAME', p.[schema], p.name, t.text FROM #proctext t 
JOIN #procedure p on t.procid = p.id
WHERE text like '%'+@word+'%'

DROP TABLE #proctext
DROP TABLE #procedure

GO

CREATE PROCEDURE dbo.#SearchAllProcsForName 
	@word NVARCHAR(100) --searched word
AS

DECLARE @dbname SYSNAME
DECLARE Cur1 CURSOR FOR  SELECT name FROM sys.databases
OPEN cur1
FETCH NEXT FROM Cur1 INTO @dbname
WHILE @@FETCH_STATUS =0
BEGIN
	EXECUTE dbo.#SearchProcsForName @dbname, @word
	FETCH NEXT FROM cur1 INTO @dbname
END

CLOSE cur1
DEALLOCATE cur1
GO
#SearchAllProcsForName 'mail'