August 10, 2011 at 2:32 am
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,
August 10, 2011 at 3:28 am
select * from sys.procedures
August 10, 2011 at 1:36 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2011 at 2:01 pm
You can't see objects if you don't have permissions on it.
August 10, 2011 at 11:36 pm
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.
October 6, 2013 at 10:06 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply