Get the database name in which Stored Procedure exists

Question

I am having SQL server 2008 and i am having 10 different databases in it and now i want to search one stored procedure that in which database the stored procedure is present.

Mentioned as duplicate by some ..... with out reading my question properly. My Requirement is i need to verify 'SP_Email' procedure. I which database is this procedure exists.

Answer 1

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.

Answer 2

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'''

Answer 3

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)