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
'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= ''
@sql=@sql+' select '''+name+''' as database_name from '+name+'.sys.objects where name =''procedure_name'' union all' from sys.databases
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 posthttp://beyondrelational.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx
Tags: t-sql, sql_server, undocumented_procedures,
You may want to check this thread and my solutions for the same problem
Thanks Naomi for that thread