Get the name of the database in which this procedure is stored

Question

I have SQL Server 2008, and I have 10 different databases, and now I want to find one stored procedure in which the stored procedure is stored.

Mentioned as a duplicate by some ..... without reading my question correctly. My requirement is that I need to check the "SP_Email" procedure. I which database is this procedure exists.

Answer 1

You can try the following:

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

Answer 2

sys.databases , , db_name.sys.procedures, , .

:

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)

Answer 3

SELECT OBJECT_ID('DataBase1.SchemaName.StoredProcedureName') / 
OBJECT_ID('DataBase2.SchemaName.StoredProcedureName') / 
OBJECT_ID('DataBase3.SchemaName.StoredProcedureName') /
...

He will refund NULLif there is no such procedure. This will work if all databases are in the same instance.