Hi,

I am having nearly 10 databases in database server with more than 1000 objects. Now, i got a stored procedure written in SQL Server 2000. I am having it's name as "spGetCustomerByName".

Can anybody tell how to find the exact databasename that this stored procedure in present in?

I am using SQL Server 2000. I am not creator of database objects, so i am not confirmed about its location.

A.D.Ramkumar

__________________
Ramkumar A D

Here is a quick'n'dirty solution... Not pretty but it works!



---------------------------------------------------------------------
declare @proc_name nvarchar(100)
select @proc_name = 'uspPeriodsOpen'

create table #db (z smallint identity(0, 1), dbname nvarchar (100), present tinyint)

insert into #db (dbname)
EXEC sp_MSforeachdb @command1="select '?'"

declare @z smallint, @maxz smallint, @sql nvarchar (500), @db nvarchar (100)

select @z = 0,
@maxz = max(z)
from #db

while @z <= @maxz
   begin
select @db = quotename(dbname)
from #db
where z = @z

select @sql = 'update #db set present = (select count(*) from ' + @db + '..sysobjects where name = ''' + convert(nvarchar, @proc_name) + ''') where z = ' + convert(nvarchar, @z)

exec sp_executesql @sql

select @z = @z + 1
   end


select dbname from #db where present = 1

drop table #db

Kindly check this article for the reply:

http://www.sql-server-performance.co...se_objects.asp

A.D.Ramkumar

These are all fine scripts, but they all return the name of all databases, even of there are not match...