Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure Location Expand / Collapse
Author
Message
Posted Wednesday, August 10, 2011 2:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 27, 2014 12:20 AM
Points: 244, Visits: 884
Hi All,

I'm trying to work out how to find out where and how many times a particular stored procedure is located, I thought I can find out in the sys objects tables but I guess I was wrong. I would be very grateful if some would let me know please.

Thank you,
Post #1157456
Posted Wednesday, August 10, 2011 3:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 13, 2014 4:10 AM
Points: 2,296, Visits: 1,424
select * from sys.procedures
Post #1157476
Posted Wednesday, August 10, 2011 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:24 PM
Points: 11,990, Visits: 11,007
select * from sysobjects where xtype = 'p'

Will return the same list. What I don't quite understand is what exactly are you looking for?


I'm trying to work out how to find out where and how many times a particular stored procedure is located


Each stored procedure is stored in the database where it was created and by definition it can only be there once.

Are you trying to find which database(s) a particular named stored proc is located across all databases on a given server? Like you have a GetEmployeeList in several databases and you want to find which databases that one resides in? If the latter is what you are after you will have to use the officially undocumented system procedure "sp_msforeachdb".


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1157983
Posted Wednesday, August 10, 2011 2:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
You can't see objects if you don't have permissions on it.
Post #1158010
Posted Wednesday, August 10, 2011 11:36 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:19 AM
Points: 1,101, Visits: 5,271
tt-615680 (8/10/2011)
Hi All,

I'm trying to work out how to find out where and how many times a particular stored procedure is located, I thought I can find out in the sys objects tables but I guess I was wrong. I would be very grateful if some would let me know please.

Thank you,
Tizita

In your post, it is not clear whether you are searching within a single database or all the databases.

If you are searching for multiple occurances of a stored procedure within a single database...
I think the stored procedure is created on different schemas. Join sys.schemas to sys.objects. That should give you correct result.

If you are searching all the databases use sp_msforeachdb.

Post #1158195
Posted Sunday, October 06, 2013 10:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:45 AM
Points: 14, Visits: 59
If you need to know where the undocumented SP are, master system stored procedures. viewing them may give you good insights on how to utilize them.
Post #1501926
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse