Search Procedure name in all databases

Often I see people asking questions in the forums about finding the database name where the particular procedure exists
You can use many methods to get a solution (cursor, undocumented procedure,dynamic sql,etc)

The simplest are

1 Use Undocumented procedure

 
EXEC sp_msforeachdb 
'if exists(select ''?'' from ?.sys.objects where name=''your_procedure_name'')
select ''?'' from ?.sys.objects where name=''your_procedure_name'''

2 Use Dynamic SQL

 
declare @sql varchar(max)

set @sql= ''

select 
	@sql=@sql+' select '''+name+''' as database_name from '+name+'.sys.objects where name =''procedure_name'' union all' from sys.databases 

select @sql=substring(@sql,1,len(@sql)-9)

exec(@sql)

Note that method 1 is not recommedted as it uses undocumented stored procedure sp_msforeachdb
which may be removed from future release of the SQL Server.

Also refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan 3 · 34% · 5789