There are many different ways to accomplish this tasks.

The below examples uses undocumented sp’s to loop through all the user defined database to search the SP’s using given string

SCRIPT 1 -- without Search String Parameter but you need to change the search pattern in SQL(Replace in place of Index_Name in the below SQL)

 

SQL
Edit|Remove
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 
 

 

index2

SCRIPT 2 -- With Search String Parameter

The below SQL uses SearchString as a parameter also it uses syscomments system view

 

SQL
Edit|Remove
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 
 

index1

Reference Thread

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/857d9754-ac24-4acb-b402-e52d028bb81a/linked-servers-migration?forum=sqlgetstarted#9c7fec83-610d-4ec6-9b27-9f685031b571