way to search for a stored procedure in object browser of sql server 2

[saadabc]

Is there a way in sql server to search an entire server for a stored procedure name - i.e. the stored procedure could be in any one of the databases in the server.

I'm looking at a .net program (that somebody wrote) that is running a stored procedure in sql server - one thing is that I can't find the servername and database name that the application is connecting to. the structure of the application is a little wierd. secondly, I look in the databases that I'm pretty sure it's referencing and I don't see that stored procedure in the list. It's wierd.

 

[NoCoolHandle]

Sounded like a reasonable challenge..
Try

Create Proc FindProc
@procname varchar(300)
as
declare @db varchar(300)
set @db =''
while not @db is null
   begin     
	select @db = min(name) from sysdatabases where name > @db
     exec ( 'if exists(select *   from ' + @db + '..sysobjects where type=''p'' and name = ''' + @procname + ''') begin print ''' + @db + ' ''end' )
   end
go
 FindProc  'sp_help'

 

[DrSql]

This one is also similar logic but it might give you more details.

[tt]
use master
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp]
GO
CREATE TABLE [dbo].[temp] (
[data] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp1]
GO
CREATE TABLE [dbo].[temp1] (
[data] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]
GO


declare @exists int
declare @db varchar(255)
declare @dbsql varchar(300)
declare @objname varchar(255)
declare cur_ObjFinder cursor for
select name from master.dbo.sysdatabases
open cur_ObjFinder
readnext:
fetch next from cur_ObjFinder into @db
If @@FETCH_STATUS <> 0
goto eof
--Make chnages to SP in thi stmnt
select @dbsql='Select name from '+@db+'.dbo.sysobjects where name like '+char(39)+'%SP%'+char(39)
--print @dbsql
insert into master.dbo.temp exec(@dbsql)
select @objname=data from master.dbo.temp
If @objname is not Null
begin
insert into master.dbo.temp1 (data) values (@db+': '+@objname)
--Print 'Object Found:'+@objname
delete master.dbo.temp
end

goto readnext

eof:
close cur_ObjFinder
deallocate cur_ObjFinder

drop table master.dbo.temp
select * from master.dbo.temp1 order by 1
drop table master.dbo.temp1
[/tt]

Dr.Sql
Good Luck.

 

[gmmastros]

Here's an alternative that does not loop but uses the undocumented sp_MsForEachDb function.

Alter  Procedure FindProcedure
	@ProcedureName VarChar(255)
AS
SET NOCOUNT ON

Declare @SQL VarChar(1000)

Set @SQL = 'Select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME 
            From [?].Information_Schema.Routines 
            Where SPECIFIC_NAME = ''' + @ProcedureName + ''''

Create Table #Temp(SPECIFIC_CATALOG VarChar(100), SPECIFIC_SCHEMA VarChar(100), SPECIFIC_NAME VarChar(100))

Insert Into #Temp
Exec sp_msforeachdb @SQL

Select * from #Temp

Drop Table #Temp

go

FindProcedure 'sp_Help'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[saadabc]

Awesome Guys!!!

 

[saadabc]

NoCoolHandle - there's no sysdatabases.

 

[NoCoolHandle]

sysdatabases exists in the master database
Sorry did't think through all the ways it might be used

try changing the query to
[/code]
Create Proc FindProc
@procname varchar(300)
as
declare @db varchar(300)
set @db =''
while not @db is null
begin
select @db = min(name) from [red]master..[/red]sysdatabases where name > @db
exec ( 'if exists(select * from ' + @db + '..sysobjects where type=''p'' and name = ''' + @procname + ''') begin print ''' + @db + ' ''end' )
end
go
FindProc 'sp_help'
[/code]

 

[saadabc]

I'm trying gmmaestos and DrSQL's methods

It's giving me :

'Microsoft SQL Server Driver: Communication Link Error'

 

[gmmastros]

The "Communication Like Error" means you are having network issues.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom