There are many different ways to accomplish this tasks.
Download Link https://gallery.technet.microsoft.com/T-SQL-How-to-Search-String-a1704fc6
The below examples uses undocumented sp’s to loop through all the user defined database to search the SP’s using given string
CREATE TABLE #ProcSearch (databaseName VARCHAR(100),ROUTINE_CATALOG varchar(50),ProcName VARCHAR(128),RoutineType varchar(100),CreateDate date)
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?
INSERT INTO #ProcSearch
SELECT ''?'',ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_TYPE, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ''%Index_Name%'' AND (ROUTINE_TYPE=''PROCEDURE'' or ROUTINE_TYPE=''FUNCTION'');
END'
EXEC sp_MSforeachdb @command
SELECT * FROM #ProcSearch
DROP TABLE #ProcSearch
The below SQL uses SearchString as a parameter also it uses syscomments system view
CREATE TABLE #ProcSearch ([databaseNAme] Varchar(100),ProcName VARCHAR(128))
DECLARE @command varchar(1000)
Declare @SearchString varchar(100)='Index_Name'
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?
INSERT INTO #ProcSearch ([databaseNAme],[ProcName])
SELECT ''?'', OBJECT_NAME(id) as ROUTINE_CATALOG FROM syscomments WHERE [text] LIKE ''%'+@SearchString+'%'' AND OBJECTPROPERTY(id, ''IsProcedure'') = 1 GROUP BY OBJECT_NAME(id)
END'
EXEC sp_MSforeachdb @command
SELECT * FROM #ProcSearch
Reference Thread
