Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is the search order for Procedures prefixed sp_?


What is the search order for Procedures prefixed sp_?

Author
Message
Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: Moderators
Points: 9744 Visits: 780
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp



David.Poole
David.Poole
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5837 Visits: 3228
Interesting

I've always insisted that stored procedures were prefixed with usp (user stored procedure) so that I can spot the ones for which I should expect documentation.

As there are an ever growing number of internal stored procedures it is vital that non-internal stored procedures are immediately identifiable, if only to show that you don't have to trawl for BOL to find out what they were supposed to do.

LinkedIn Profile

Newbie on www.simple-talk.com
Simon Sabin
Simon Sabin
Say Hey Kid
Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)

Group: General Forum Members
Points: 672 Visits: 107
It would be great if you could categorise stored procedures into folders. So that in EM you have Stored Procedures Then a System folder and a User folder and you can then add more folders under the User folder.

Who nows what we get with Yukon, I know we are getting auto complete like in visual studio, gone will be the days of trying to remember column names.

Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons
Jonr
Jonr
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 65
Good article James, well investigated.
Looks like Microsoft got it right on the security front with this one too, as the s/p needs to be owned by a system user in order to exec sp_MS_marksystemobject.

Jon Reade


Jon
Jonr
Jonr
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 65
Good article James, well investigated.
Looks like Microsoft got it right on the security front with this one too, as the s/p needs to be owned by a system user in order to exec sp_MS_marksystemobject.

Jon Reade


Jon
Paul Thornett
Paul Thornett
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 96
Well, my experience is absolutely the opposite of James Travis - and is exactly what I would have expected!

Here is what I did, using Sql Server 2000 Sp2 under Windows 2000 professional Sp3:

- Opened Query Analyzer
- Typed in the following:
use master
go
create procedure sp_mysp as
select 'I am the master'
go
use miscellaneous
go
exec miscellaneous..sp_mysp
go
create procedure sp_mysp as
select 'I am NOT the master'
go
exec miscellaneous..sp__mysp

and got exactly the same as James
I am the master
I am NOT the master
- I closed Sql Server 2000 (SP2) down, then restarted it
- Then
use miscellaneous
go
exec miscellaneous..sp_mysp
STILL gives
I am NOT the master
- And
sp__mysp
ALSO gives
I am NOT the master
- And
use Maintenance
go
exec sp_mysp
gives
I am the master

So how do we explain these 2 sets of opposing results?



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (8.6K reputation)

Group: Moderators
Points: 8624 Visits: 1917
Did you mark the stored procedure in the master database as a system object? I don't see that you did based on your explanation. That would explain the discrepancy based on both the original forum discussion and the article.

K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@‌kbriankelley
Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: Moderators
Points: 9744 Visits: 780
I believe Brain is right. If you look back I mark the sp as a system object by running

sp_MS_marksystemobject

against it.

It is not a documented procedure but what it does is change the bitmask of the status of the object to match with system type. Problem is once done there is no real safe way to undo (can be done don't get me wrong) and as long as marked as a system type you cannot drop thru EM (sorry didn't try thru QA and wasn't really worried since is a test server I got to rebuild in the next few months anyway). Once you do that and restart the server, your results should match up.



Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: Moderators
Points: 9744 Visits: 780
quote:

It would be great if you could categorise stored procedures into folders. So that in EM you have Stored Procedures Then a System folder and a User folder and you can then add more folders under the User folder.

Who nows what we get with Yukon, I know we are getting auto complete like in visual studio, gone will be the days of trying to remember column names.

Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088



Yes, that would be a great feature. This one may be a future possiblity thou if you look at what they did with QA in SQL 2000 and the schema view window (or whatever it is called).



Paul Thornett
Paul Thornett
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 96
OK, having run sp_ms_marksystemobject, I get the same results as James.

But the article was entitled 'What is the search order for Procedures prefixed sp_?'
In that context, running sp_ms_marksystemobject is not relevant. And I would therefore argue that there is no reason why user-written stored procedures should not be named with the 'sp_' prefix. I would also suggest that having 2 stored procedures with the same name, where one of them resides within master, is poor management.

My own convention is to prefix any sps I write which I store into master with 'sp__' (that's 2 underscores instead of 1). So I get the benefit of a universally available sp, but at the same time can easily see which sps within master are 'mine'.

In other dbs, I use sprName for sps I expect to invoke directly (usually from code within my app), and spr_ for sps that are not invoked directly by me, but instead are invoked from within the (higher level) sprName sps.

And fortunately, I operate in an environment where I have complete and sole control over all such standards and conventions <g>!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search