store procedure location - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

store procedure location

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-14 : 00:35:42

i have the store procedue name.

is it possible to find where does this store proc located in which db and server?

is there any command to search?

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-14 : 01:33:22

You can find your all user defined procedure in your database tree.
For looking then expand your database then expand programmability
then here you will find all stored procdures.

Vijay is here to learn something from you guys.

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-05-14 : 02:04:24

There's alot of database and store procedure.
That is why I am asking is there any command to do so?

Thanks

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-14 : 04:50:37

SELECT name AS spname
FROM sysobjects
WHERE (xtype = 'p') AND (name NOT LIKE 'dt%')
ORDER BY name

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-14 : 22:49:29

quote:
Originally posted by peace

There's alot of database and store procedure.
That is why I am asking is there any command to do so?

Thanks

But If its all in same server but you dont know the db, you can use the below


EXEC sp_MSforeachdb 'IF EXISTS(SELECT 1 FROM ?.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = ''<your proc name here>'') SELECT ''?'''

this will give you dbname where it resides in the server

if you've multiple servers and you dont which server sp resides then you've iterate the query for each server i guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources