Get the database name in which Stored Procedure exists

you need to query sys.databases of master database to get list of databases and for each database name you get you need to query the db_name.sys.procedures to check if it exists.

try below query and give a feedback:

use master
go
declare @FullQuery varchar(max)
declare @DBName varchar(50)
set @FullQuery=''
declare cr cursor for select name from sys.databases where database_id > 4
open cr
fetch next from cr into @DBName
while(@@fetch_status=0)
begin
set @FullQuery=@FullQuery+
    ' select name  COLLATE SQL_Latin1_General_CP1_CI_AS from '+@DBName+'.sys.procedures where name like ''%proc_name%'' union'
fetch next from cr into @DBName
end
close cr
deallocate cr
set @FullQuery=substring(@FullQuery,1,len(@FullQuery)-5)
exec (@FullQuery)

You can try this:

EXEC sp_msforeachdb 
'if exists(select 1 from [?].sys.objects where name=''SP_Email'')
select ''?'' as FoundInDatabase from [?].sys.objects where name=''SP_Email'''

Please try this.

SELECT name DatabaseName
FROM sys.databases
WHERE OBJECT_ID(QUOTENAME(name) + '.dbo.ProcedureNameHere', 'P') IS NOT NULL;

This will return the database(s) name in which this particular object exist.

Replace ProcedureNameHere with your procedure name. In your case it would be SP_Email Keep rest of the things as it is.