finding information about the Stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

finding information about the Stored procedure

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-14 : 00:49:00

Hi,

I am trying to make a query that goes through all the databases on a server (let's say that a server is constant) and finds a particular stored procedure.

The reason for this is that I have large amount of C# code and most of the time it contains SPROCS. There are like 15 databases on the server and I don't know which one contains the SPROC. If some how I can find which database contains the SPROC then it can speed up the development.

Thanks,

Mohammad Azam
www.azamsharp.net

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-14 : 02:49:11

use the Object Search (F4 key) in Query Analyser

-----------------
'KH'
if you can't beat them, have someone else to beat them

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-14 : 09:30:31

>>I am trying to make a query that...
If you need a programmatic version this should do it:


declare @object_name sysname
set @object_name = '<Object to be found>'


declare @dbid int
	,@oid int
	,@sql nvarchar(500)
	,@parms nvarchar(200)
set @parms = N'@oid int output'

--itterate through all databases on server
select @dbid = min(dbid) from master..sysdatabases
while @dbid is not null
begin
	--set a command string to look for object in <current database>
	set @sql = 'select @oid = object_id(''' + db_name(@dbid) + '..' + @object_name + ''')'

	--execute the command
	exec sp_executesql @sql, @parms, @oid = @oid output

	--if id is found print the name of <current database>
	if @oid > 0
		print db_name(@dbid)

	--get next database
	select @dbid = min(dbid) from master..sysdatabases where dbid > @dbid
end

Be One with the Optimizer
TG

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-14 : 10:20:19

AWESOME! Works like a charm :) Thanks a million!

Mohammad Azam
www.azamsharp.net

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-16 : 03:14:31

Also in Query Analyser, run this

Select 'Select specific_catalog from '+Catalog_name+'.information_Schema.routines where Specific_catalog=''mysp'''
as Query from information_Schema.schemata

Copy the result and paste it at QA and run them

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources