As i know SP_prcedure name are reserved for system procedure, somhow i have just come on this database project and found there are more than 50 SP are already made by someOne by sp_ prefix. i want to know what possible wrong with that can happen..? i know need to avoid this prefix so i sud worry and do something or let it go as it is... and this datbase already on live production.
In addition to sp_ being slower, it can also potentially make your SP unavailable. As far as I've been taught, if there is an SP in master with the same name as your local SP in your user-database, there's a way to execute the local SP. Even if you use the three-part-name databasename.dbo.sp_theproc, SQL Server will first look to see if there's an SP in master with that name, and if there is, it will execute the master stored proc in the database context you've as databasename in the three-part name. Here's a script that shows just this. USE myLocalDB GO CREATE PROC dbo.sp_help AS PRINT 'This is the local version of sp_help' GO EXEC dbo.sp_help GO EXEC myLocalDB.dbo.sp_help GO
thanks somewhere it is exactly what i want to know. now i can add this to in my plan as well to change the name after checking all other things. but one last question in my mind is same what is written in blog.. "do we really know this to be truth, or do have we simply taken someone’s word on it, treated it as sacred cow, or simply restated it for years it because it sounds good"..cause for me its a person blog view only.?
As far as I'm aware, SP_ doesn't stand for 'stored procedure' at all and was put in by Sybase a long time ago to mean 'Special'. It has long been mistaken for an early bit of reverse-hungarian notation. It designates an object as being a special system object in the master database. I seem to remember Kalen Delaney saying in a presentation that you can prefix a table with sp_ as well and have some weird effects happen. In Sybase, all objects prefixed with sp_ are searched in the master as well as the current database. Procedures created without this prefix, are expected to be stored exclusively in the user's current database. It would be great if someone could do some research on this topic, because I'm a bit unsure of what actually happens.