Use this code:
NOTE:Replace ur Sp which you need to
search in "
Sales"
DECLARE @SQL NVARCHAR(max)
,@spName VARCHAR(100) = 'Sales'
SELECT @SQL = STUFF((
SELECT ' UNION ALL
SELECT ' + quotename(NAME, '''') + ' AS Db_Name FROM ' + quotename(NAME) + '.INFORMATION_SCHEMA.Routines
WHERE ROUTINE_NAME = @spName AND ROUTINE_TYPE = ''PROCEDURE'''
FROM sys.databases
ORDER BY NAME
FOR XML PATH('')
,TYPE
).value('.', 'nvarchar(max)'), 1, 11, '')
EXECUTE sp_executeSQL @SQL
,N'@spName varchar(100)'
,@spName