CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rahul Tripathi
Rahul Tripathi

Reputation: 172448

Find database name from the stored procedure name

I want to know the name of the database in which my stored procedure is there. I have used the below query to find it but it is not showing me the database name:-

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

It is only showing me the message:

Command(s) completed successfully.

What I am missing? Please help.

Upvotes: 0

Views: 2045

Answers (2)

podiluska
podiluska

Reputation: 51494

You don't have a procedure of that name.

As an aside, it's not a good idea to prefix stored procedures with SP_

http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156978

I guess that is because the procedure sp_CreateEmail doesn't exist in your database. When I execute your code for the procedure sp_MSrepl_startup (which is in my master database), it shows one row.

Upvotes: 1

Related Questions