Searching Stored Procs

Question

Post reply

Searching Stored Procs

James-319013

SSC Rookie

Points: 44
More actions
February 28, 2007 at 1:05 pm

#62546

Does anyone know a way off to seach through all databases on a SQL 2000 server looking for a particular stored proc by name?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 1

You can use the store procedure: sp_msforeachdb to run the query against every database on the system.

http://www.mssqlcity.com/FAQ/Devel/sp_msforeachdb.htm

James-319013 SSC Rookie Points: 44 More actions · Answer 2

Thank you, that get's me much closer. Now I just have to find a way to isolate the stored proc that SQL Profiler is listing. Thanks again.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

You should be able to use the DBid column in Profiler to determine which database the stored procedure is comming from.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

James-319013 SSC Rookie Points: 44 More actions · Answer 4

Yep, but I'm a n00b and still trying to work out how to do that.

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Well, when you set up your trace, make sure that the DatabaseID column is listed in the 'Data Columns' tab on the Trace Properties. You can then cross reference this DBID with the following:

SELECT dbid, Name

FROM master..sysdatabases

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

James-319013 SSC Rookie Points: 44 More actions · Answer 6

James-319013

SSC Rookie

Points: 44

February 28, 2007 at 2:05 pm

#692428

Thanks John, you have opened the eyes of the n00b!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

James

I take your original question as meaning that you're looking for the existence of the stored procedure, rather than its use. If that's the case you don't need a Profiler trace. Just run this query against each database, using sp_MSforeachdb if you like:

SELECT ROUTINE_CATALOG + '.' + ROUTINE_SCHEMA + '.' + ROUTINE_NAME

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = 'PROCEDURE'

John